1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self):
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self):
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    @property
 242    def meta(self) -> t.Dict[str, t.Any]:
 243        if self._meta is None:
 244            self._meta = {}
 245        return self._meta
 246
 247    def __deepcopy__(self, memo):
 248        copy = self.__class__(**deepcopy(self.args))
 249        if self.comments is not None:
 250            copy.comments = deepcopy(self.comments)
 251
 252        if self._type is not None:
 253            copy._type = self._type.copy()
 254
 255        if self._meta is not None:
 256            copy._meta = deepcopy(self._meta)
 257
 258        return copy
 259
 260    def copy(self):
 261        """
 262        Returns a deep copy of the expression.
 263        """
 264        new = deepcopy(self)
 265        new.parent = self.parent
 266        return new
 267
 268    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 269        if self.comments is None:
 270            self.comments = []
 271        if comments:
 272            for comment in comments:
 273                _, *meta = comment.split(SQLGLOT_META)
 274                if meta:
 275                    for kv in "".join(meta).split(","):
 276                        k, *v = kv.split("=")
 277                        value = v[0].strip() if v else True
 278                        self.meta[k.strip()] = value
 279                self.comments.append(comment)
 280
 281    def append(self, arg_key: str, value: t.Any) -> None:
 282        """
 283        Appends value to arg_key if it's a list or sets it as a new list.
 284
 285        Args:
 286            arg_key (str): name of the list expression arg
 287            value (Any): value to append to the list
 288        """
 289        if not isinstance(self.args.get(arg_key), list):
 290            self.args[arg_key] = []
 291        self.args[arg_key].append(value)
 292        self._set_parent(arg_key, value)
 293
 294    def set(self, arg_key: str, value: t.Any) -> None:
 295        """
 296        Sets arg_key to value.
 297
 298        Args:
 299            arg_key: name of the expression arg.
 300            value: value to set the arg to.
 301        """
 302        if value is None:
 303            self.args.pop(arg_key, None)
 304            return
 305
 306        self.args[arg_key] = value
 307        self._set_parent(arg_key, value)
 308
 309    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 310        if hasattr(value, "parent"):
 311            value.parent = self
 312            value.arg_key = arg_key
 313        elif type(value) is list:
 314            for v in value:
 315                if hasattr(v, "parent"):
 316                    v.parent = self
 317                    v.arg_key = arg_key
 318
 319    @property
 320    def depth(self) -> int:
 321        """
 322        Returns the depth of this tree.
 323        """
 324        if self.parent:
 325            return self.parent.depth + 1
 326        return 0
 327
 328    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 329        """Yields the key and expression for all arguments, exploding list args."""
 330        for k, vs in self.args.items():
 331            if type(vs) is list:
 332                for v in vs:
 333                    if hasattr(v, "parent"):
 334                        yield k, v
 335            else:
 336                if hasattr(vs, "parent"):
 337                    yield k, vs
 338
 339    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 340        """
 341        Returns the first node in this tree which matches at least one of
 342        the specified types.
 343
 344        Args:
 345            expression_types: the expression type(s) to match.
 346            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 347
 348        Returns:
 349            The node which matches the criteria or None if no such node was found.
 350        """
 351        return next(self.find_all(*expression_types, bfs=bfs), None)
 352
 353    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 354        """
 355        Returns a generator object which visits all nodes in this tree and only
 356        yields those that match at least one of the specified expression types.
 357
 358        Args:
 359            expression_types: the expression type(s) to match.
 360            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 361
 362        Returns:
 363            The generator object.
 364        """
 365        for expression, *_ in self.walk(bfs=bfs):
 366            if isinstance(expression, expression_types):
 367                yield expression
 368
 369    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 370        """
 371        Returns a nearest parent matching expression_types.
 372
 373        Args:
 374            expression_types: the expression type(s) to match.
 375
 376        Returns:
 377            The parent node.
 378        """
 379        ancestor = self.parent
 380        while ancestor and not isinstance(ancestor, expression_types):
 381            ancestor = ancestor.parent
 382        return t.cast(E, ancestor)
 383
 384    @property
 385    def parent_select(self) -> t.Optional[Select]:
 386        """
 387        Returns the parent select statement.
 388        """
 389        return self.find_ancestor(Select)
 390
 391    @property
 392    def same_parent(self) -> bool:
 393        """Returns if the parent is the same class as itself."""
 394        return type(self.parent) is self.__class__
 395
 396    def root(self) -> Expression:
 397        """
 398        Returns the root expression of this tree.
 399        """
 400        expression = self
 401        while expression.parent:
 402            expression = expression.parent
 403        return expression
 404
 405    def walk(self, bfs=True, prune=None):
 406        """
 407        Returns a generator object which visits all nodes in this tree.
 408
 409        Args:
 410            bfs (bool): if set to True the BFS traversal order will be applied,
 411                otherwise the DFS traversal will be used instead.
 412            prune ((node, parent, arg_key) -> bool): callable that returns True if
 413                the generator should stop traversing this branch of the tree.
 414
 415        Returns:
 416            the generator object.
 417        """
 418        if bfs:
 419            yield from self.bfs(prune=prune)
 420        else:
 421            yield from self.dfs(prune=prune)
 422
 423    def dfs(self, parent=None, key=None, prune=None):
 424        """
 425        Returns a generator object which visits all nodes in this tree in
 426        the DFS (Depth-first) order.
 427
 428        Returns:
 429            The generator object.
 430        """
 431        parent = parent or self.parent
 432        yield self, parent, key
 433        if prune and prune(self, parent, key):
 434            return
 435
 436        for k, v in self.iter_expressions():
 437            yield from v.dfs(self, k, prune)
 438
 439    def bfs(self, prune=None):
 440        """
 441        Returns a generator object which visits all nodes in this tree in
 442        the BFS (Breadth-first) order.
 443
 444        Returns:
 445            The generator object.
 446        """
 447        queue = deque([(self, self.parent, None)])
 448
 449        while queue:
 450            item, parent, key = queue.popleft()
 451
 452            yield item, parent, key
 453            if prune and prune(item, parent, key):
 454                continue
 455
 456            for k, v in item.iter_expressions():
 457                queue.append((v, item, k))
 458
 459    def unnest(self):
 460        """
 461        Returns the first non parenthesis child or self.
 462        """
 463        expression = self
 464        while type(expression) is Paren:
 465            expression = expression.this
 466        return expression
 467
 468    def unalias(self):
 469        """
 470        Returns the inner expression if this is an Alias.
 471        """
 472        if isinstance(self, Alias):
 473            return self.this
 474        return self
 475
 476    def unnest_operands(self):
 477        """
 478        Returns unnested operands as a tuple.
 479        """
 480        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 481
 482    def flatten(self, unnest=True):
 483        """
 484        Returns a generator which yields child nodes who's parents are the same class.
 485
 486        A AND B AND C -> [A, B, C]
 487        """
 488        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 489            if not type(node) is self.__class__:
 490                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 491
 492    def __str__(self) -> str:
 493        return self.sql()
 494
 495    def __repr__(self) -> str:
 496        return self._to_s()
 497
 498    def sql(self, dialect: DialectType = None, **opts) -> str:
 499        """
 500        Returns SQL string representation of this tree.
 501
 502        Args:
 503            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 504            opts: other `sqlglot.generator.Generator` options.
 505
 506        Returns:
 507            The SQL string.
 508        """
 509        from sqlglot.dialects import Dialect
 510
 511        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 512
 513    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 514        indent = "" if not level else "\n"
 515        indent += "".join(["  "] * level)
 516        left = f"({self.key.upper()} "
 517
 518        args: t.Dict[str, t.Any] = {
 519            k: ", ".join(
 520                v._to_s(hide_missing=hide_missing, level=level + 1)
 521                if hasattr(v, "_to_s")
 522                else str(v)
 523                for v in ensure_list(vs)
 524                if v is not None
 525            )
 526            for k, vs in self.args.items()
 527        }
 528        args["comments"] = self.comments
 529        args["type"] = self.type
 530        args = {k: v for k, v in args.items() if v or not hide_missing}
 531
 532        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 533        right += ")"
 534
 535        return indent + left + right
 536
 537    def transform(self, fun, *args, copy=True, **kwargs):
 538        """
 539        Recursively visits all tree nodes (excluding already transformed ones)
 540        and applies the given transformation function to each node.
 541
 542        Args:
 543            fun (function): a function which takes a node as an argument and returns a
 544                new transformed node or the same node without modifications. If the function
 545                returns None, then the corresponding node will be removed from the syntax tree.
 546            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 547                modified in place.
 548
 549        Returns:
 550            The transformed tree.
 551        """
 552        node = self.copy() if copy else self
 553        new_node = fun(node, *args, **kwargs)
 554
 555        if new_node is None or not isinstance(new_node, Expression):
 556            return new_node
 557        if new_node is not node:
 558            new_node.parent = node.parent
 559            return new_node
 560
 561        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 562        return new_node
 563
 564    @t.overload
 565    def replace(self, expression: E) -> E:
 566        ...
 567
 568    @t.overload
 569    def replace(self, expression: None) -> None:
 570        ...
 571
 572    def replace(self, expression):
 573        """
 574        Swap out this expression with a new expression.
 575
 576        For example::
 577
 578            >>> tree = Select().select("x").from_("tbl")
 579            >>> tree.find(Column).replace(Column(this="y"))
 580            (COLUMN this: y)
 581            >>> tree.sql()
 582            'SELECT y FROM tbl'
 583
 584        Args:
 585            expression: new node
 586
 587        Returns:
 588            The new expression or expressions.
 589        """
 590        if not self.parent:
 591            return expression
 592
 593        parent = self.parent
 594        self.parent = None
 595
 596        replace_children(parent, lambda child: expression if child is self else child)
 597        return expression
 598
 599    def pop(self: E) -> E:
 600        """
 601        Remove this expression from its AST.
 602
 603        Returns:
 604            The popped expression.
 605        """
 606        self.replace(None)
 607        return self
 608
 609    def assert_is(self, type_: t.Type[E]) -> E:
 610        """
 611        Assert that this `Expression` is an instance of `type_`.
 612
 613        If it is NOT an instance of `type_`, this raises an assertion error.
 614        Otherwise, this returns this expression.
 615
 616        Examples:
 617            This is useful for type security in chained expressions:
 618
 619            >>> import sqlglot
 620            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 621            'SELECT x, z FROM y'
 622        """
 623        assert isinstance(self, type_)
 624        return self
 625
 626    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 627        """
 628        Checks if this expression is valid (e.g. all mandatory args are set).
 629
 630        Args:
 631            args: a sequence of values that were used to instantiate a Func expression. This is used
 632                to check that the provided arguments don't exceed the function argument limit.
 633
 634        Returns:
 635            A list of error messages for all possible errors that were found.
 636        """
 637        errors: t.List[str] = []
 638
 639        for k in self.args:
 640            if k not in self.arg_types:
 641                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 642        for k, mandatory in self.arg_types.items():
 643            v = self.args.get(k)
 644            if mandatory and (v is None or (isinstance(v, list) and not v)):
 645                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 646
 647        if (
 648            args
 649            and isinstance(self, Func)
 650            and len(args) > len(self.arg_types)
 651            and not self.is_var_len_args
 652        ):
 653            errors.append(
 654                f"The number of provided arguments ({len(args)}) is greater than "
 655                f"the maximum number of supported arguments ({len(self.arg_types)})"
 656            )
 657
 658        return errors
 659
 660    def dump(self):
 661        """
 662        Dump this Expression to a JSON-serializable dict.
 663        """
 664        from sqlglot.serde import dump
 665
 666        return dump(self)
 667
 668    @classmethod
 669    def load(cls, obj):
 670        """
 671        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 672        """
 673        from sqlglot.serde import load
 674
 675        return load(obj)
 676
 677    def and_(
 678        self,
 679        *expressions: t.Optional[ExpOrStr],
 680        dialect: DialectType = None,
 681        copy: bool = True,
 682        **opts,
 683    ) -> Condition:
 684        """
 685        AND this condition with one or multiple expressions.
 686
 687        Example:
 688            >>> condition("x=1").and_("y=1").sql()
 689            'x = 1 AND y = 1'
 690
 691        Args:
 692            *expressions: the SQL code strings to parse.
 693                If an `Expression` instance is passed, it will be used as-is.
 694            dialect: the dialect used to parse the input expression.
 695            copy: whether or not to copy the involved expressions (only applies to Expressions).
 696            opts: other options to use to parse the input expressions.
 697
 698        Returns:
 699            The new And condition.
 700        """
 701        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 702
 703    def or_(
 704        self,
 705        *expressions: t.Optional[ExpOrStr],
 706        dialect: DialectType = None,
 707        copy: bool = True,
 708        **opts,
 709    ) -> Condition:
 710        """
 711        OR this condition with one or multiple expressions.
 712
 713        Example:
 714            >>> condition("x=1").or_("y=1").sql()
 715            'x = 1 OR y = 1'
 716
 717        Args:
 718            *expressions: the SQL code strings to parse.
 719                If an `Expression` instance is passed, it will be used as-is.
 720            dialect: the dialect used to parse the input expression.
 721            copy: whether or not to copy the involved expressions (only applies to Expressions).
 722            opts: other options to use to parse the input expressions.
 723
 724        Returns:
 725            The new Or condition.
 726        """
 727        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 728
 729    def not_(self, copy: bool = True):
 730        """
 731        Wrap this condition with NOT.
 732
 733        Example:
 734            >>> condition("x=1").not_().sql()
 735            'NOT x = 1'
 736
 737        Args:
 738            copy: whether or not to copy this object.
 739
 740        Returns:
 741            The new Not instance.
 742        """
 743        return not_(self, copy=copy)
 744
 745    def as_(
 746        self,
 747        alias: str | Identifier,
 748        quoted: t.Optional[bool] = None,
 749        dialect: DialectType = None,
 750        copy: bool = True,
 751        **opts,
 752    ) -> Alias:
 753        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 754
 755    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 756        this = self.copy()
 757        other = convert(other, copy=True)
 758        if not isinstance(this, klass) and not isinstance(other, klass):
 759            this = _wrap(this, Binary)
 760            other = _wrap(other, Binary)
 761        if reverse:
 762            return klass(this=other, expression=this)
 763        return klass(this=this, expression=other)
 764
 765    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 766        return Bracket(
 767            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 768        )
 769
 770    def __iter__(self) -> t.Iterator:
 771        if "expressions" in self.arg_types:
 772            return iter(self.args.get("expressions") or [])
 773        # We define this because __getitem__ converts Expression into an iterable, which is
 774        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 775        # See: https://peps.python.org/pep-0234/
 776        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 777
 778    def isin(
 779        self,
 780        *expressions: t.Any,
 781        query: t.Optional[ExpOrStr] = None,
 782        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 783        copy: bool = True,
 784        **opts,
 785    ) -> In:
 786        return In(
 787            this=maybe_copy(self, copy),
 788            expressions=[convert(e, copy=copy) for e in expressions],
 789            query=maybe_parse(query, copy=copy, **opts) if query else None,
 790            unnest=Unnest(
 791                expressions=[
 792                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 793                ]
 794            )
 795            if unnest
 796            else None,
 797        )
 798
 799    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 800        return Between(
 801            this=maybe_copy(self, copy),
 802            low=convert(low, copy=copy, **opts),
 803            high=convert(high, copy=copy, **opts),
 804        )
 805
 806    def is_(self, other: ExpOrStr) -> Is:
 807        return self._binop(Is, other)
 808
 809    def like(self, other: ExpOrStr) -> Like:
 810        return self._binop(Like, other)
 811
 812    def ilike(self, other: ExpOrStr) -> ILike:
 813        return self._binop(ILike, other)
 814
 815    def eq(self, other: t.Any) -> EQ:
 816        return self._binop(EQ, other)
 817
 818    def neq(self, other: t.Any) -> NEQ:
 819        return self._binop(NEQ, other)
 820
 821    def rlike(self, other: ExpOrStr) -> RegexpLike:
 822        return self._binop(RegexpLike, other)
 823
 824    def __lt__(self, other: t.Any) -> LT:
 825        return self._binop(LT, other)
 826
 827    def __le__(self, other: t.Any) -> LTE:
 828        return self._binop(LTE, other)
 829
 830    def __gt__(self, other: t.Any) -> GT:
 831        return self._binop(GT, other)
 832
 833    def __ge__(self, other: t.Any) -> GTE:
 834        return self._binop(GTE, other)
 835
 836    def __add__(self, other: t.Any) -> Add:
 837        return self._binop(Add, other)
 838
 839    def __radd__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other, reverse=True)
 841
 842    def __sub__(self, other: t.Any) -> Sub:
 843        return self._binop(Sub, other)
 844
 845    def __rsub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other, reverse=True)
 847
 848    def __mul__(self, other: t.Any) -> Mul:
 849        return self._binop(Mul, other)
 850
 851    def __rmul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other, reverse=True)
 853
 854    def __truediv__(self, other: t.Any) -> Div:
 855        return self._binop(Div, other)
 856
 857    def __rtruediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other, reverse=True)
 859
 860    def __floordiv__(self, other: t.Any) -> IntDiv:
 861        return self._binop(IntDiv, other)
 862
 863    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other, reverse=True)
 865
 866    def __mod__(self, other: t.Any) -> Mod:
 867        return self._binop(Mod, other)
 868
 869    def __rmod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other, reverse=True)
 871
 872    def __pow__(self, other: t.Any) -> Pow:
 873        return self._binop(Pow, other)
 874
 875    def __rpow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other, reverse=True)
 877
 878    def __and__(self, other: t.Any) -> And:
 879        return self._binop(And, other)
 880
 881    def __rand__(self, other: t.Any) -> And:
 882        return self._binop(And, other, reverse=True)
 883
 884    def __or__(self, other: t.Any) -> Or:
 885        return self._binop(Or, other)
 886
 887    def __ror__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other, reverse=True)
 889
 890    def __neg__(self) -> Neg:
 891        return Neg(this=_wrap(self.copy(), Binary))
 892
 893    def __invert__(self) -> Not:
 894        return not_(self.copy())
 895
 896
 897IntoType = t.Union[
 898    str,
 899    t.Type[Expression],
 900    t.Collection[t.Union[str, t.Type[Expression]]],
 901]
 902ExpOrStr = t.Union[str, Expression]
 903
 904
 905class Condition(Expression):
 906    """Logical conditions like x AND y, or simply x"""
 907
 908
 909class Predicate(Condition):
 910    """Relationships like x = y, x > 1, x >= y."""
 911
 912
 913class DerivedTable(Expression):
 914    @property
 915    def selects(self) -> t.List[Expression]:
 916        return self.this.selects if isinstance(self.this, Subqueryable) else []
 917
 918    @property
 919    def named_selects(self) -> t.List[str]:
 920        return [select.output_name for select in self.selects]
 921
 922
 923class Unionable(Expression):
 924    def union(
 925        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 926    ) -> Unionable:
 927        """
 928        Builds a UNION expression.
 929
 930        Example:
 931            >>> import sqlglot
 932            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 933            'SELECT * FROM foo UNION SELECT * FROM bla'
 934
 935        Args:
 936            expression: the SQL code string.
 937                If an `Expression` instance is passed, it will be used as-is.
 938            distinct: set the DISTINCT flag if and only if this is true.
 939            dialect: the dialect used to parse the input expression.
 940            opts: other options to use to parse the input expressions.
 941
 942        Returns:
 943            The new Union expression.
 944        """
 945        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 946
 947    def intersect(
 948        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 949    ) -> Unionable:
 950        """
 951        Builds an INTERSECT expression.
 952
 953        Example:
 954            >>> import sqlglot
 955            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 956            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 957
 958        Args:
 959            expression: the SQL code string.
 960                If an `Expression` instance is passed, it will be used as-is.
 961            distinct: set the DISTINCT flag if and only if this is true.
 962            dialect: the dialect used to parse the input expression.
 963            opts: other options to use to parse the input expressions.
 964
 965        Returns:
 966            The new Intersect expression.
 967        """
 968        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 969
 970    def except_(
 971        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 972    ) -> Unionable:
 973        """
 974        Builds an EXCEPT expression.
 975
 976        Example:
 977            >>> import sqlglot
 978            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 979            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 980
 981        Args:
 982            expression: the SQL code string.
 983                If an `Expression` instance is passed, it will be used as-is.
 984            distinct: set the DISTINCT flag if and only if this is true.
 985            dialect: the dialect used to parse the input expression.
 986            opts: other options to use to parse the input expressions.
 987
 988        Returns:
 989            The new Except expression.
 990        """
 991        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 992
 993
 994class UDTF(DerivedTable, Unionable):
 995    @property
 996    def selects(self) -> t.List[Expression]:
 997        alias = self.args.get("alias")
 998        return alias.columns if alias else []
 999
1000
1001class Cache(Expression):
1002    arg_types = {
1003        "with": False,
1004        "this": True,
1005        "lazy": False,
1006        "options": False,
1007        "expression": False,
1008    }
1009
1010
1011class Uncache(Expression):
1012    arg_types = {"this": True, "exists": False}
1013
1014
1015class DDL(Expression):
1016    @property
1017    def ctes(self):
1018        with_ = self.args.get("with")
1019        if not with_:
1020            return []
1021        return with_.expressions
1022
1023    @property
1024    def named_selects(self) -> t.List[str]:
1025        if isinstance(self.expression, Subqueryable):
1026            return self.expression.named_selects
1027        return []
1028
1029    @property
1030    def selects(self) -> t.List[Expression]:
1031        if isinstance(self.expression, Subqueryable):
1032            return self.expression.selects
1033        return []
1034
1035
1036class Create(DDL):
1037    arg_types = {
1038        "with": False,
1039        "this": True,
1040        "kind": True,
1041        "expression": False,
1042        "exists": False,
1043        "properties": False,
1044        "replace": False,
1045        "unique": False,
1046        "indexes": False,
1047        "no_schema_binding": False,
1048        "begin": False,
1049        "end": False,
1050        "clone": False,
1051    }
1052
1053
1054# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1055# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1056# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1057class Clone(Expression):
1058    arg_types = {
1059        "this": True,
1060        "when": False,
1061        "kind": False,
1062        "shallow": False,
1063        "expression": False,
1064        "copy": False,
1065    }
1066
1067
1068class Describe(Expression):
1069    arg_types = {"this": True, "kind": False, "expressions": False}
1070
1071
1072class Kill(Expression):
1073    arg_types = {"this": True, "kind": False}
1074
1075
1076class Pragma(Expression):
1077    pass
1078
1079
1080class Set(Expression):
1081    arg_types = {"expressions": False, "unset": False, "tag": False}
1082
1083
1084class SetItem(Expression):
1085    arg_types = {
1086        "this": False,
1087        "expressions": False,
1088        "kind": False,
1089        "collate": False,  # MySQL SET NAMES statement
1090        "global": False,
1091    }
1092
1093
1094class Show(Expression):
1095    arg_types = {
1096        "this": True,
1097        "target": False,
1098        "offset": False,
1099        "limit": False,
1100        "like": False,
1101        "where": False,
1102        "db": False,
1103        "scope": False,
1104        "scope_kind": False,
1105        "full": False,
1106        "mutex": False,
1107        "query": False,
1108        "channel": False,
1109        "global": False,
1110        "log": False,
1111        "position": False,
1112        "types": False,
1113    }
1114
1115
1116class UserDefinedFunction(Expression):
1117    arg_types = {"this": True, "expressions": False, "wrapped": False}
1118
1119
1120class CharacterSet(Expression):
1121    arg_types = {"this": True, "default": False}
1122
1123
1124class With(Expression):
1125    arg_types = {"expressions": True, "recursive": False}
1126
1127    @property
1128    def recursive(self) -> bool:
1129        return bool(self.args.get("recursive"))
1130
1131
1132class WithinGroup(Expression):
1133    arg_types = {"this": True, "expression": False}
1134
1135
1136class CTE(DerivedTable):
1137    arg_types = {"this": True, "alias": True}
1138
1139
1140class TableAlias(Expression):
1141    arg_types = {"this": False, "columns": False}
1142
1143    @property
1144    def columns(self):
1145        return self.args.get("columns") or []
1146
1147
1148class BitString(Condition):
1149    pass
1150
1151
1152class HexString(Condition):
1153    pass
1154
1155
1156class ByteString(Condition):
1157    pass
1158
1159
1160class RawString(Condition):
1161    pass
1162
1163
1164class Column(Condition):
1165    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1166
1167    @property
1168    def table(self) -> str:
1169        return self.text("table")
1170
1171    @property
1172    def db(self) -> str:
1173        return self.text("db")
1174
1175    @property
1176    def catalog(self) -> str:
1177        return self.text("catalog")
1178
1179    @property
1180    def output_name(self) -> str:
1181        return self.name
1182
1183    @property
1184    def parts(self) -> t.List[Identifier]:
1185        """Return the parts of a column in order catalog, db, table, name."""
1186        return [
1187            t.cast(Identifier, self.args[part])
1188            for part in ("catalog", "db", "table", "this")
1189            if self.args.get(part)
1190        ]
1191
1192    def to_dot(self) -> Dot | Identifier:
1193        """Converts the column into a dot expression."""
1194        parts = self.parts
1195        parent = self.parent
1196
1197        while parent:
1198            if isinstance(parent, Dot):
1199                parts.append(parent.expression)
1200            parent = parent.parent
1201
1202        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1203
1204
1205class ColumnPosition(Expression):
1206    arg_types = {"this": False, "position": True}
1207
1208
1209class ColumnDef(Expression):
1210    arg_types = {
1211        "this": True,
1212        "kind": False,
1213        "constraints": False,
1214        "exists": False,
1215        "position": False,
1216    }
1217
1218    @property
1219    def constraints(self) -> t.List[ColumnConstraint]:
1220        return self.args.get("constraints") or []
1221
1222
1223class AlterColumn(Expression):
1224    arg_types = {
1225        "this": True,
1226        "dtype": False,
1227        "collate": False,
1228        "using": False,
1229        "default": False,
1230        "drop": False,
1231    }
1232
1233
1234class RenameTable(Expression):
1235    pass
1236
1237
1238class Comment(Expression):
1239    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1240
1241
1242class Comprehension(Expression):
1243    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1244
1245
1246# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1247class MergeTreeTTLAction(Expression):
1248    arg_types = {
1249        "this": True,
1250        "delete": False,
1251        "recompress": False,
1252        "to_disk": False,
1253        "to_volume": False,
1254    }
1255
1256
1257# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1258class MergeTreeTTL(Expression):
1259    arg_types = {
1260        "expressions": True,
1261        "where": False,
1262        "group": False,
1263        "aggregates": False,
1264    }
1265
1266
1267# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1268class IndexConstraintOption(Expression):
1269    arg_types = {
1270        "key_block_size": False,
1271        "using": False,
1272        "parser": False,
1273        "comment": False,
1274        "visible": False,
1275        "engine_attr": False,
1276        "secondary_engine_attr": False,
1277    }
1278
1279
1280class ColumnConstraint(Expression):
1281    arg_types = {"this": False, "kind": True}
1282
1283    @property
1284    def kind(self) -> ColumnConstraintKind:
1285        return self.args["kind"]
1286
1287
1288class ColumnConstraintKind(Expression):
1289    pass
1290
1291
1292class AutoIncrementColumnConstraint(ColumnConstraintKind):
1293    pass
1294
1295
1296class CaseSpecificColumnConstraint(ColumnConstraintKind):
1297    arg_types = {"not_": True}
1298
1299
1300class CharacterSetColumnConstraint(ColumnConstraintKind):
1301    arg_types = {"this": True}
1302
1303
1304class CheckColumnConstraint(ColumnConstraintKind):
1305    pass
1306
1307
1308class ClusteredColumnConstraint(ColumnConstraintKind):
1309    pass
1310
1311
1312class CollateColumnConstraint(ColumnConstraintKind):
1313    pass
1314
1315
1316class CommentColumnConstraint(ColumnConstraintKind):
1317    pass
1318
1319
1320class CompressColumnConstraint(ColumnConstraintKind):
1321    pass
1322
1323
1324class DateFormatColumnConstraint(ColumnConstraintKind):
1325    arg_types = {"this": True}
1326
1327
1328class DefaultColumnConstraint(ColumnConstraintKind):
1329    pass
1330
1331
1332class EncodeColumnConstraint(ColumnConstraintKind):
1333    pass
1334
1335
1336class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1337    # this: True -> ALWAYS, this: False -> BY DEFAULT
1338    arg_types = {
1339        "this": False,
1340        "expression": False,
1341        "on_null": False,
1342        "start": False,
1343        "increment": False,
1344        "minvalue": False,
1345        "maxvalue": False,
1346        "cycle": False,
1347    }
1348
1349
1350# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1351class IndexColumnConstraint(ColumnConstraintKind):
1352    arg_types = {
1353        "this": False,
1354        "schema": True,
1355        "kind": False,
1356        "index_type": False,
1357        "options": False,
1358    }
1359
1360
1361class InlineLengthColumnConstraint(ColumnConstraintKind):
1362    pass
1363
1364
1365class NonClusteredColumnConstraint(ColumnConstraintKind):
1366    pass
1367
1368
1369class NotForReplicationColumnConstraint(ColumnConstraintKind):
1370    arg_types = {}
1371
1372
1373class NotNullColumnConstraint(ColumnConstraintKind):
1374    arg_types = {"allow_null": False}
1375
1376
1377# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1378class OnUpdateColumnConstraint(ColumnConstraintKind):
1379    pass
1380
1381
1382class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1383    arg_types = {"desc": False}
1384
1385
1386class TitleColumnConstraint(ColumnConstraintKind):
1387    pass
1388
1389
1390class UniqueColumnConstraint(ColumnConstraintKind):
1391    arg_types = {"this": False, "index_type": False}
1392
1393
1394class UppercaseColumnConstraint(ColumnConstraintKind):
1395    arg_types: t.Dict[str, t.Any] = {}
1396
1397
1398class PathColumnConstraint(ColumnConstraintKind):
1399    pass
1400
1401
1402# computed column expression
1403# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1404class ComputedColumnConstraint(ColumnConstraintKind):
1405    arg_types = {"this": True, "persisted": False, "not_null": False}
1406
1407
1408class Constraint(Expression):
1409    arg_types = {"this": True, "expressions": True}
1410
1411
1412class Delete(Expression):
1413    arg_types = {
1414        "with": False,
1415        "this": False,
1416        "using": False,
1417        "where": False,
1418        "returning": False,
1419        "limit": False,
1420        "tables": False,  # Multiple-Table Syntax (MySQL)
1421    }
1422
1423    def delete(
1424        self,
1425        table: ExpOrStr,
1426        dialect: DialectType = None,
1427        copy: bool = True,
1428        **opts,
1429    ) -> Delete:
1430        """
1431        Create a DELETE expression or replace the table on an existing DELETE expression.
1432
1433        Example:
1434            >>> delete("tbl").sql()
1435            'DELETE FROM tbl'
1436
1437        Args:
1438            table: the table from which to delete.
1439            dialect: the dialect used to parse the input expression.
1440            copy: if `False`, modify this expression instance in-place.
1441            opts: other options to use to parse the input expressions.
1442
1443        Returns:
1444            Delete: the modified expression.
1445        """
1446        return _apply_builder(
1447            expression=table,
1448            instance=self,
1449            arg="this",
1450            dialect=dialect,
1451            into=Table,
1452            copy=copy,
1453            **opts,
1454        )
1455
1456    def where(
1457        self,
1458        *expressions: t.Optional[ExpOrStr],
1459        append: bool = True,
1460        dialect: DialectType = None,
1461        copy: bool = True,
1462        **opts,
1463    ) -> Delete:
1464        """
1465        Append to or set the WHERE expressions.
1466
1467        Example:
1468            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1469            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1470
1471        Args:
1472            *expressions: the SQL code strings to parse.
1473                If an `Expression` instance is passed, it will be used as-is.
1474                Multiple expressions are combined with an AND operator.
1475            append: if `True`, AND the new expressions to any existing expression.
1476                Otherwise, this resets the expression.
1477            dialect: the dialect used to parse the input expressions.
1478            copy: if `False`, modify this expression instance in-place.
1479            opts: other options to use to parse the input expressions.
1480
1481        Returns:
1482            Delete: the modified expression.
1483        """
1484        return _apply_conjunction_builder(
1485            *expressions,
1486            instance=self,
1487            arg="where",
1488            append=append,
1489            into=Where,
1490            dialect=dialect,
1491            copy=copy,
1492            **opts,
1493        )
1494
1495    def returning(
1496        self,
1497        expression: ExpOrStr,
1498        dialect: DialectType = None,
1499        copy: bool = True,
1500        **opts,
1501    ) -> Delete:
1502        """
1503        Set the RETURNING expression. Not supported by all dialects.
1504
1505        Example:
1506            >>> delete("tbl").returning("*", dialect="postgres").sql()
1507            'DELETE FROM tbl RETURNING *'
1508
1509        Args:
1510            expression: the SQL code strings to parse.
1511                If an `Expression` instance is passed, it will be used as-is.
1512            dialect: the dialect used to parse the input expressions.
1513            copy: if `False`, modify this expression instance in-place.
1514            opts: other options to use to parse the input expressions.
1515
1516        Returns:
1517            Delete: the modified expression.
1518        """
1519        return _apply_builder(
1520            expression=expression,
1521            instance=self,
1522            arg="returning",
1523            prefix="RETURNING",
1524            dialect=dialect,
1525            copy=copy,
1526            into=Returning,
1527            **opts,
1528        )
1529
1530
1531class Drop(Expression):
1532    arg_types = {
1533        "this": False,
1534        "kind": False,
1535        "exists": False,
1536        "temporary": False,
1537        "materialized": False,
1538        "cascade": False,
1539        "constraints": False,
1540        "purge": False,
1541    }
1542
1543
1544class Filter(Expression):
1545    arg_types = {"this": True, "expression": True}
1546
1547
1548class Check(Expression):
1549    pass
1550
1551
1552# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1553class Connect(Expression):
1554    arg_types = {"start": False, "connect": True}
1555
1556
1557class Prior(Expression):
1558    pass
1559
1560
1561class Directory(Expression):
1562    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1563    arg_types = {"this": True, "local": False, "row_format": False}
1564
1565
1566class ForeignKey(Expression):
1567    arg_types = {
1568        "expressions": True,
1569        "reference": False,
1570        "delete": False,
1571        "update": False,
1572    }
1573
1574
1575class ColumnPrefix(Expression):
1576    arg_types = {"this": True, "expression": True}
1577
1578
1579class PrimaryKey(Expression):
1580    arg_types = {"expressions": True, "options": False}
1581
1582
1583# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1584# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1585class Into(Expression):
1586    arg_types = {"this": True, "temporary": False, "unlogged": False}
1587
1588
1589class From(Expression):
1590    @property
1591    def name(self) -> str:
1592        return self.this.name
1593
1594    @property
1595    def alias_or_name(self) -> str:
1596        return self.this.alias_or_name
1597
1598
1599class Having(Expression):
1600    pass
1601
1602
1603class Hint(Expression):
1604    arg_types = {"expressions": True}
1605
1606
1607class JoinHint(Expression):
1608    arg_types = {"this": True, "expressions": True}
1609
1610
1611class Identifier(Expression):
1612    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1613
1614    @property
1615    def quoted(self) -> bool:
1616        return bool(self.args.get("quoted"))
1617
1618    @property
1619    def hashable_args(self) -> t.Any:
1620        return (self.this, self.quoted)
1621
1622    @property
1623    def output_name(self) -> str:
1624        return self.name
1625
1626
1627# https://www.postgresql.org/docs/current/indexes-opclass.html
1628class Opclass(Expression):
1629    arg_types = {"this": True, "expression": True}
1630
1631
1632class Index(Expression):
1633    arg_types = {
1634        "this": False,
1635        "table": False,
1636        "using": False,
1637        "where": False,
1638        "columns": False,
1639        "unique": False,
1640        "primary": False,
1641        "amp": False,  # teradata
1642        "partition_by": False,  # teradata
1643        "where": False,  # postgres partial indexes
1644    }
1645
1646
1647class Insert(DDL):
1648    arg_types = {
1649        "with": False,
1650        "this": True,
1651        "expression": False,
1652        "conflict": False,
1653        "returning": False,
1654        "overwrite": False,
1655        "exists": False,
1656        "partition": False,
1657        "alternative": False,
1658        "where": False,
1659        "ignore": False,
1660        "by_name": False,
1661    }
1662
1663    def with_(
1664        self,
1665        alias: ExpOrStr,
1666        as_: ExpOrStr,
1667        recursive: t.Optional[bool] = None,
1668        append: bool = True,
1669        dialect: DialectType = None,
1670        copy: bool = True,
1671        **opts,
1672    ) -> Insert:
1673        """
1674        Append to or set the common table expressions.
1675
1676        Example:
1677            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1678            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1679
1680        Args:
1681            alias: the SQL code string to parse as the table name.
1682                If an `Expression` instance is passed, this is used as-is.
1683            as_: the SQL code string to parse as the table expression.
1684                If an `Expression` instance is passed, it will be used as-is.
1685            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1686            append: if `True`, add to any existing expressions.
1687                Otherwise, this resets the expressions.
1688            dialect: the dialect used to parse the input expression.
1689            copy: if `False`, modify this expression instance in-place.
1690            opts: other options to use to parse the input expressions.
1691
1692        Returns:
1693            The modified expression.
1694        """
1695        return _apply_cte_builder(
1696            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1697        )
1698
1699
1700class OnConflict(Expression):
1701    arg_types = {
1702        "duplicate": False,
1703        "expressions": False,
1704        "nothing": False,
1705        "key": False,
1706        "constraint": False,
1707    }
1708
1709
1710class Returning(Expression):
1711    arg_types = {"expressions": True, "into": False}
1712
1713
1714# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1715class Introducer(Expression):
1716    arg_types = {"this": True, "expression": True}
1717
1718
1719# national char, like n'utf8'
1720class National(Expression):
1721    pass
1722
1723
1724class LoadData(Expression):
1725    arg_types = {
1726        "this": True,
1727        "local": False,
1728        "overwrite": False,
1729        "inpath": True,
1730        "partition": False,
1731        "input_format": False,
1732        "serde": False,
1733    }
1734
1735
1736class Partition(Expression):
1737    arg_types = {"expressions": True}
1738
1739
1740class Fetch(Expression):
1741    arg_types = {
1742        "direction": False,
1743        "count": False,
1744        "percent": False,
1745        "with_ties": False,
1746    }
1747
1748
1749class Group(Expression):
1750    arg_types = {
1751        "expressions": False,
1752        "grouping_sets": False,
1753        "cube": False,
1754        "rollup": False,
1755        "totals": False,
1756        "all": False,
1757    }
1758
1759
1760class Lambda(Expression):
1761    arg_types = {"this": True, "expressions": True}
1762
1763
1764class Limit(Expression):
1765    arg_types = {"this": False, "expression": True, "offset": False}
1766
1767
1768class Literal(Condition):
1769    arg_types = {"this": True, "is_string": True}
1770
1771    @property
1772    def hashable_args(self) -> t.Any:
1773        return (self.this, self.args.get("is_string"))
1774
1775    @classmethod
1776    def number(cls, number) -> Literal:
1777        return cls(this=str(number), is_string=False)
1778
1779    @classmethod
1780    def string(cls, string) -> Literal:
1781        return cls(this=str(string), is_string=True)
1782
1783    @property
1784    def output_name(self) -> str:
1785        return self.name
1786
1787
1788class Join(Expression):
1789    arg_types = {
1790        "this": True,
1791        "on": False,
1792        "side": False,
1793        "kind": False,
1794        "using": False,
1795        "method": False,
1796        "global": False,
1797        "hint": False,
1798    }
1799
1800    @property
1801    def method(self) -> str:
1802        return self.text("method").upper()
1803
1804    @property
1805    def kind(self) -> str:
1806        return self.text("kind").upper()
1807
1808    @property
1809    def side(self) -> str:
1810        return self.text("side").upper()
1811
1812    @property
1813    def hint(self) -> str:
1814        return self.text("hint").upper()
1815
1816    @property
1817    def alias_or_name(self) -> str:
1818        return self.this.alias_or_name
1819
1820    def on(
1821        self,
1822        *expressions: t.Optional[ExpOrStr],
1823        append: bool = True,
1824        dialect: DialectType = None,
1825        copy: bool = True,
1826        **opts,
1827    ) -> Join:
1828        """
1829        Append to or set the ON expressions.
1830
1831        Example:
1832            >>> import sqlglot
1833            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1834            'JOIN x ON y = 1'
1835
1836        Args:
1837            *expressions: the SQL code strings to parse.
1838                If an `Expression` instance is passed, it will be used as-is.
1839                Multiple expressions are combined with an AND operator.
1840            append: if `True`, AND the new expressions to any existing expression.
1841                Otherwise, this resets the expression.
1842            dialect: the dialect used to parse the input expressions.
1843            copy: if `False`, modify this expression instance in-place.
1844            opts: other options to use to parse the input expressions.
1845
1846        Returns:
1847            The modified Join expression.
1848        """
1849        join = _apply_conjunction_builder(
1850            *expressions,
1851            instance=self,
1852            arg="on",
1853            append=append,
1854            dialect=dialect,
1855            copy=copy,
1856            **opts,
1857        )
1858
1859        if join.kind == "CROSS":
1860            join.set("kind", None)
1861
1862        return join
1863
1864    def using(
1865        self,
1866        *expressions: t.Optional[ExpOrStr],
1867        append: bool = True,
1868        dialect: DialectType = None,
1869        copy: bool = True,
1870        **opts,
1871    ) -> Join:
1872        """
1873        Append to or set the USING expressions.
1874
1875        Example:
1876            >>> import sqlglot
1877            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1878            'JOIN x USING (foo, bla)'
1879
1880        Args:
1881            *expressions: the SQL code strings to parse.
1882                If an `Expression` instance is passed, it will be used as-is.
1883            append: if `True`, concatenate the new expressions to the existing "using" list.
1884                Otherwise, this resets the expression.
1885            dialect: the dialect used to parse the input expressions.
1886            copy: if `False`, modify this expression instance in-place.
1887            opts: other options to use to parse the input expressions.
1888
1889        Returns:
1890            The modified Join expression.
1891        """
1892        join = _apply_list_builder(
1893            *expressions,
1894            instance=self,
1895            arg="using",
1896            append=append,
1897            dialect=dialect,
1898            copy=copy,
1899            **opts,
1900        )
1901
1902        if join.kind == "CROSS":
1903            join.set("kind", None)
1904
1905        return join
1906
1907
1908class Lateral(UDTF):
1909    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1910
1911
1912class MatchRecognize(Expression):
1913    arg_types = {
1914        "partition_by": False,
1915        "order": False,
1916        "measures": False,
1917        "rows": False,
1918        "after": False,
1919        "pattern": False,
1920        "define": False,
1921        "alias": False,
1922    }
1923
1924
1925# Clickhouse FROM FINAL modifier
1926# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1927class Final(Expression):
1928    pass
1929
1930
1931class Offset(Expression):
1932    arg_types = {"this": False, "expression": True}
1933
1934
1935class Order(Expression):
1936    arg_types = {"this": False, "expressions": True}
1937
1938
1939# hive specific sorts
1940# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1941class Cluster(Order):
1942    pass
1943
1944
1945class Distribute(Order):
1946    pass
1947
1948
1949class Sort(Order):
1950    pass
1951
1952
1953class Ordered(Expression):
1954    arg_types = {"this": True, "desc": False, "nulls_first": True}
1955
1956
1957class Property(Expression):
1958    arg_types = {"this": True, "value": True}
1959
1960
1961class AlgorithmProperty(Property):
1962    arg_types = {"this": True}
1963
1964
1965class AutoIncrementProperty(Property):
1966    arg_types = {"this": True}
1967
1968
1969class BlockCompressionProperty(Property):
1970    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1971
1972
1973class CharacterSetProperty(Property):
1974    arg_types = {"this": True, "default": True}
1975
1976
1977class ChecksumProperty(Property):
1978    arg_types = {"on": False, "default": False}
1979
1980
1981class CollateProperty(Property):
1982    arg_types = {"this": True}
1983
1984
1985class CopyGrantsProperty(Property):
1986    arg_types = {}
1987
1988
1989class DataBlocksizeProperty(Property):
1990    arg_types = {
1991        "size": False,
1992        "units": False,
1993        "minimum": False,
1994        "maximum": False,
1995        "default": False,
1996    }
1997
1998
1999class DefinerProperty(Property):
2000    arg_types = {"this": True}
2001
2002
2003class DistKeyProperty(Property):
2004    arg_types = {"this": True}
2005
2006
2007class DistStyleProperty(Property):
2008    arg_types = {"this": True}
2009
2010
2011class EngineProperty(Property):
2012    arg_types = {"this": True}
2013
2014
2015class HeapProperty(Property):
2016    arg_types = {}
2017
2018
2019class ToTableProperty(Property):
2020    arg_types = {"this": True}
2021
2022
2023class ExecuteAsProperty(Property):
2024    arg_types = {"this": True}
2025
2026
2027class ExternalProperty(Property):
2028    arg_types = {"this": False}
2029
2030
2031class FallbackProperty(Property):
2032    arg_types = {"no": True, "protection": False}
2033
2034
2035class FileFormatProperty(Property):
2036    arg_types = {"this": True}
2037
2038
2039class FreespaceProperty(Property):
2040    arg_types = {"this": True, "percent": False}
2041
2042
2043class InputModelProperty(Property):
2044    arg_types = {"this": True}
2045
2046
2047class OutputModelProperty(Property):
2048    arg_types = {"this": True}
2049
2050
2051class IsolatedLoadingProperty(Property):
2052    arg_types = {
2053        "no": True,
2054        "concurrent": True,
2055        "for_all": True,
2056        "for_insert": True,
2057        "for_none": True,
2058    }
2059
2060
2061class JournalProperty(Property):
2062    arg_types = {
2063        "no": False,
2064        "dual": False,
2065        "before": False,
2066        "local": False,
2067        "after": False,
2068    }
2069
2070
2071class LanguageProperty(Property):
2072    arg_types = {"this": True}
2073
2074
2075# spark ddl
2076class ClusteredByProperty(Property):
2077    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2078
2079
2080class DictProperty(Property):
2081    arg_types = {"this": True, "kind": True, "settings": False}
2082
2083
2084class DictSubProperty(Property):
2085    pass
2086
2087
2088class DictRange(Property):
2089    arg_types = {"this": True, "min": True, "max": True}
2090
2091
2092# Clickhouse CREATE ... ON CLUSTER modifier
2093# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2094class OnCluster(Property):
2095    arg_types = {"this": True}
2096
2097
2098class LikeProperty(Property):
2099    arg_types = {"this": True, "expressions": False}
2100
2101
2102class LocationProperty(Property):
2103    arg_types = {"this": True}
2104
2105
2106class LockingProperty(Property):
2107    arg_types = {
2108        "this": False,
2109        "kind": True,
2110        "for_or_in": False,
2111        "lock_type": True,
2112        "override": False,
2113    }
2114
2115
2116class LogProperty(Property):
2117    arg_types = {"no": True}
2118
2119
2120class MaterializedProperty(Property):
2121    arg_types = {"this": False}
2122
2123
2124class MergeBlockRatioProperty(Property):
2125    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2126
2127
2128class NoPrimaryIndexProperty(Property):
2129    arg_types = {}
2130
2131
2132class OnProperty(Property):
2133    arg_types = {"this": True}
2134
2135
2136class OnCommitProperty(Property):
2137    arg_types = {"delete": False}
2138
2139
2140class PartitionedByProperty(Property):
2141    arg_types = {"this": True}
2142
2143
2144class RemoteWithConnectionModelProperty(Property):
2145    arg_types = {"this": True}
2146
2147
2148class ReturnsProperty(Property):
2149    arg_types = {"this": True, "is_table": False, "table": False}
2150
2151
2152class RowFormatProperty(Property):
2153    arg_types = {"this": True}
2154
2155
2156class RowFormatDelimitedProperty(Property):
2157    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2158    arg_types = {
2159        "fields": False,
2160        "escaped": False,
2161        "collection_items": False,
2162        "map_keys": False,
2163        "lines": False,
2164        "null": False,
2165        "serde": False,
2166    }
2167
2168
2169class RowFormatSerdeProperty(Property):
2170    arg_types = {"this": True, "serde_properties": False}
2171
2172
2173# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2174class QueryTransform(Expression):
2175    arg_types = {
2176        "expressions": True,
2177        "command_script": True,
2178        "schema": False,
2179        "row_format_before": False,
2180        "record_writer": False,
2181        "row_format_after": False,
2182        "record_reader": False,
2183    }
2184
2185
2186class SampleProperty(Property):
2187    arg_types = {"this": True}
2188
2189
2190class SchemaCommentProperty(Property):
2191    arg_types = {"this": True}
2192
2193
2194class SerdeProperties(Property):
2195    arg_types = {"expressions": True}
2196
2197
2198class SetProperty(Property):
2199    arg_types = {"multi": True}
2200
2201
2202class SettingsProperty(Property):
2203    arg_types = {"expressions": True}
2204
2205
2206class SortKeyProperty(Property):
2207    arg_types = {"this": True, "compound": False}
2208
2209
2210class SqlSecurityProperty(Property):
2211    arg_types = {"definer": True}
2212
2213
2214class StabilityProperty(Property):
2215    arg_types = {"this": True}
2216
2217
2218class TemporaryProperty(Property):
2219    arg_types = {}
2220
2221
2222class TransformModelProperty(Property):
2223    arg_types = {"expressions": True}
2224
2225
2226class TransientProperty(Property):
2227    arg_types = {"this": False}
2228
2229
2230class VolatileProperty(Property):
2231    arg_types = {"this": False}
2232
2233
2234class WithDataProperty(Property):
2235    arg_types = {"no": True, "statistics": False}
2236
2237
2238class WithJournalTableProperty(Property):
2239    arg_types = {"this": True}
2240
2241
2242class Properties(Expression):
2243    arg_types = {"expressions": True}
2244
2245    NAME_TO_PROPERTY = {
2246        "ALGORITHM": AlgorithmProperty,
2247        "AUTO_INCREMENT": AutoIncrementProperty,
2248        "CHARACTER SET": CharacterSetProperty,
2249        "CLUSTERED_BY": ClusteredByProperty,
2250        "COLLATE": CollateProperty,
2251        "COMMENT": SchemaCommentProperty,
2252        "DEFINER": DefinerProperty,
2253        "DISTKEY": DistKeyProperty,
2254        "DISTSTYLE": DistStyleProperty,
2255        "ENGINE": EngineProperty,
2256        "EXECUTE AS": ExecuteAsProperty,
2257        "FORMAT": FileFormatProperty,
2258        "LANGUAGE": LanguageProperty,
2259        "LOCATION": LocationProperty,
2260        "PARTITIONED_BY": PartitionedByProperty,
2261        "RETURNS": ReturnsProperty,
2262        "ROW_FORMAT": RowFormatProperty,
2263        "SORTKEY": SortKeyProperty,
2264    }
2265
2266    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2267
2268    # CREATE property locations
2269    # Form: schema specified
2270    #   create [POST_CREATE]
2271    #     table a [POST_NAME]
2272    #     (b int) [POST_SCHEMA]
2273    #     with ([POST_WITH])
2274    #     index (b) [POST_INDEX]
2275    #
2276    # Form: alias selection
2277    #   create [POST_CREATE]
2278    #     table a [POST_NAME]
2279    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2280    #     index (c) [POST_INDEX]
2281    class Location(AutoName):
2282        POST_CREATE = auto()
2283        POST_NAME = auto()
2284        POST_SCHEMA = auto()
2285        POST_WITH = auto()
2286        POST_ALIAS = auto()
2287        POST_EXPRESSION = auto()
2288        POST_INDEX = auto()
2289        UNSUPPORTED = auto()
2290
2291    @classmethod
2292    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2293        expressions = []
2294        for key, value in properties_dict.items():
2295            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2296            if property_cls:
2297                expressions.append(property_cls(this=convert(value)))
2298            else:
2299                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2300
2301        return cls(expressions=expressions)
2302
2303
2304class Qualify(Expression):
2305    pass
2306
2307
2308class InputOutputFormat(Expression):
2309    arg_types = {"input_format": False, "output_format": False}
2310
2311
2312# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2313class Return(Expression):
2314    pass
2315
2316
2317class Reference(Expression):
2318    arg_types = {"this": True, "expressions": False, "options": False}
2319
2320
2321class Tuple(Expression):
2322    arg_types = {"expressions": False}
2323
2324    def isin(
2325        self,
2326        *expressions: t.Any,
2327        query: t.Optional[ExpOrStr] = None,
2328        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2329        copy: bool = True,
2330        **opts,
2331    ) -> In:
2332        return In(
2333            this=maybe_copy(self, copy),
2334            expressions=[convert(e, copy=copy) for e in expressions],
2335            query=maybe_parse(query, copy=copy, **opts) if query else None,
2336            unnest=Unnest(
2337                expressions=[
2338                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2339                ]
2340            )
2341            if unnest
2342            else None,
2343        )
2344
2345
2346class Subqueryable(Unionable):
2347    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2348        """
2349        Convert this expression to an aliased expression that can be used as a Subquery.
2350
2351        Example:
2352            >>> subquery = Select().select("x").from_("tbl").subquery()
2353            >>> Select().select("x").from_(subquery).sql()
2354            'SELECT x FROM (SELECT x FROM tbl)'
2355
2356        Args:
2357            alias (str | Identifier): an optional alias for the subquery
2358            copy (bool): if `False`, modify this expression instance in-place.
2359
2360        Returns:
2361            Alias: the subquery
2362        """
2363        instance = maybe_copy(self, copy)
2364        if not isinstance(alias, Expression):
2365            alias = TableAlias(this=to_identifier(alias)) if alias else None
2366
2367        return Subquery(this=instance, alias=alias)
2368
2369    def limit(
2370        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2371    ) -> Select:
2372        raise NotImplementedError
2373
2374    @property
2375    def ctes(self):
2376        with_ = self.args.get("with")
2377        if not with_:
2378            return []
2379        return with_.expressions
2380
2381    @property
2382    def selects(self) -> t.List[Expression]:
2383        raise NotImplementedError("Subqueryable objects must implement `selects`")
2384
2385    @property
2386    def named_selects(self) -> t.List[str]:
2387        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2388
2389    def select(
2390        self,
2391        *expressions: t.Optional[ExpOrStr],
2392        append: bool = True,
2393        dialect: DialectType = None,
2394        copy: bool = True,
2395        **opts,
2396    ) -> Subqueryable:
2397        raise NotImplementedError("Subqueryable objects must implement `select`")
2398
2399    def with_(
2400        self,
2401        alias: ExpOrStr,
2402        as_: ExpOrStr,
2403        recursive: t.Optional[bool] = None,
2404        append: bool = True,
2405        dialect: DialectType = None,
2406        copy: bool = True,
2407        **opts,
2408    ) -> Subqueryable:
2409        """
2410        Append to or set the common table expressions.
2411
2412        Example:
2413            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2414            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2415
2416        Args:
2417            alias: the SQL code string to parse as the table name.
2418                If an `Expression` instance is passed, this is used as-is.
2419            as_: the SQL code string to parse as the table expression.
2420                If an `Expression` instance is passed, it will be used as-is.
2421            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2422            append: if `True`, add to any existing expressions.
2423                Otherwise, this resets the expressions.
2424            dialect: the dialect used to parse the input expression.
2425            copy: if `False`, modify this expression instance in-place.
2426            opts: other options to use to parse the input expressions.
2427
2428        Returns:
2429            The modified expression.
2430        """
2431        return _apply_cte_builder(
2432            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2433        )
2434
2435
2436QUERY_MODIFIERS = {
2437    "match": False,
2438    "laterals": False,
2439    "joins": False,
2440    "connect": False,
2441    "pivots": False,
2442    "where": False,
2443    "group": False,
2444    "having": False,
2445    "qualify": False,
2446    "windows": False,
2447    "distribute": False,
2448    "sort": False,
2449    "cluster": False,
2450    "order": False,
2451    "limit": False,
2452    "offset": False,
2453    "locks": False,
2454    "sample": False,
2455    "settings": False,
2456    "format": False,
2457}
2458
2459
2460# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2461class WithTableHint(Expression):
2462    arg_types = {"expressions": True}
2463
2464
2465# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2466class IndexTableHint(Expression):
2467    arg_types = {"this": True, "expressions": False, "target": False}
2468
2469
2470class Table(Expression):
2471    arg_types = {
2472        "this": True,
2473        "alias": False,
2474        "db": False,
2475        "catalog": False,
2476        "laterals": False,
2477        "joins": False,
2478        "pivots": False,
2479        "hints": False,
2480        "system_time": False,
2481        "version": False,
2482        "format": False,
2483        "pattern": False,
2484        "index": False,
2485    }
2486
2487    @property
2488    def name(self) -> str:
2489        if isinstance(self.this, Func):
2490            return ""
2491        return self.this.name
2492
2493    @property
2494    def db(self) -> str:
2495        return self.text("db")
2496
2497    @property
2498    def catalog(self) -> str:
2499        return self.text("catalog")
2500
2501    @property
2502    def selects(self) -> t.List[Expression]:
2503        return []
2504
2505    @property
2506    def named_selects(self) -> t.List[str]:
2507        return []
2508
2509    @property
2510    def parts(self) -> t.List[Expression]:
2511        """Return the parts of a table in order catalog, db, table."""
2512        parts: t.List[Expression] = []
2513
2514        for arg in ("catalog", "db", "this"):
2515            part = self.args.get(arg)
2516
2517            if isinstance(part, Dot):
2518                parts.extend(part.flatten())
2519            elif isinstance(part, Expression):
2520                parts.append(part)
2521
2522        return parts
2523
2524
2525class Union(Subqueryable):
2526    arg_types = {
2527        "with": False,
2528        "this": True,
2529        "expression": True,
2530        "distinct": False,
2531        "by_name": False,
2532        **QUERY_MODIFIERS,
2533    }
2534
2535    def limit(
2536        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2537    ) -> Select:
2538        """
2539        Set the LIMIT expression.
2540
2541        Example:
2542            >>> select("1").union(select("1")).limit(1).sql()
2543            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2544
2545        Args:
2546            expression: the SQL code string to parse.
2547                This can also be an integer.
2548                If a `Limit` instance is passed, this is used as-is.
2549                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2550            dialect: the dialect used to parse the input expression.
2551            copy: if `False`, modify this expression instance in-place.
2552            opts: other options to use to parse the input expressions.
2553
2554        Returns:
2555            The limited subqueryable.
2556        """
2557        return (
2558            select("*")
2559            .from_(self.subquery(alias="_l_0", copy=copy))
2560            .limit(expression, dialect=dialect, copy=False, **opts)
2561        )
2562
2563    def select(
2564        self,
2565        *expressions: t.Optional[ExpOrStr],
2566        append: bool = True,
2567        dialect: DialectType = None,
2568        copy: bool = True,
2569        **opts,
2570    ) -> Union:
2571        """Append to or set the SELECT of the union recursively.
2572
2573        Example:
2574            >>> from sqlglot import parse_one
2575            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2576            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2577
2578        Args:
2579            *expressions: the SQL code strings to parse.
2580                If an `Expression` instance is passed, it will be used as-is.
2581            append: if `True`, add to any existing expressions.
2582                Otherwise, this resets the expressions.
2583            dialect: the dialect used to parse the input expressions.
2584            copy: if `False`, modify this expression instance in-place.
2585            opts: other options to use to parse the input expressions.
2586
2587        Returns:
2588            Union: the modified expression.
2589        """
2590        this = self.copy() if copy else self
2591        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2592        this.expression.unnest().select(
2593            *expressions, append=append, dialect=dialect, copy=False, **opts
2594        )
2595        return this
2596
2597    @property
2598    def named_selects(self) -> t.List[str]:
2599        return self.this.unnest().named_selects
2600
2601    @property
2602    def is_star(self) -> bool:
2603        return self.this.is_star or self.expression.is_star
2604
2605    @property
2606    def selects(self) -> t.List[Expression]:
2607        return self.this.unnest().selects
2608
2609    @property
2610    def left(self):
2611        return self.this
2612
2613    @property
2614    def right(self):
2615        return self.expression
2616
2617
2618class Except(Union):
2619    pass
2620
2621
2622class Intersect(Union):
2623    pass
2624
2625
2626class Unnest(UDTF):
2627    arg_types = {
2628        "expressions": True,
2629        "alias": False,
2630        "offset": False,
2631    }
2632
2633
2634class Update(Expression):
2635    arg_types = {
2636        "with": False,
2637        "this": False,
2638        "expressions": True,
2639        "from": False,
2640        "where": False,
2641        "returning": False,
2642        "order": False,
2643        "limit": False,
2644    }
2645
2646
2647class Values(UDTF):
2648    arg_types = {
2649        "expressions": True,
2650        "ordinality": False,
2651        "alias": False,
2652    }
2653
2654
2655class Var(Expression):
2656    pass
2657
2658
2659class Version(Expression):
2660    """
2661    Time travel, iceberg, bigquery etc
2662    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2663    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2664    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2665    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2666    this is either TIMESTAMP or VERSION
2667    kind is ("AS OF", "BETWEEN")
2668    """
2669
2670    arg_types = {"this": True, "kind": True, "expression": False}
2671
2672
2673class Schema(Expression):
2674    arg_types = {"this": False, "expressions": False}
2675
2676
2677# https://dev.mysql.com/doc/refman/8.0/en/select.html
2678# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2679class Lock(Expression):
2680    arg_types = {"update": True, "expressions": False, "wait": False}
2681
2682
2683class Select(Subqueryable):
2684    arg_types = {
2685        "with": False,
2686        "kind": False,
2687        "expressions": False,
2688        "hint": False,
2689        "distinct": False,
2690        "into": False,
2691        "from": False,
2692        **QUERY_MODIFIERS,
2693    }
2694
2695    def from_(
2696        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2697    ) -> Select:
2698        """
2699        Set the FROM expression.
2700
2701        Example:
2702            >>> Select().from_("tbl").select("x").sql()
2703            'SELECT x FROM tbl'
2704
2705        Args:
2706            expression : the SQL code strings to parse.
2707                If a `From` instance is passed, this is used as-is.
2708                If another `Expression` instance is passed, it will be wrapped in a `From`.
2709            dialect: the dialect used to parse the input expression.
2710            copy: if `False`, modify this expression instance in-place.
2711            opts: other options to use to parse the input expressions.
2712
2713        Returns:
2714            The modified Select expression.
2715        """
2716        return _apply_builder(
2717            expression=expression,
2718            instance=self,
2719            arg="from",
2720            into=From,
2721            prefix="FROM",
2722            dialect=dialect,
2723            copy=copy,
2724            **opts,
2725        )
2726
2727    def group_by(
2728        self,
2729        *expressions: t.Optional[ExpOrStr],
2730        append: bool = True,
2731        dialect: DialectType = None,
2732        copy: bool = True,
2733        **opts,
2734    ) -> Select:
2735        """
2736        Set the GROUP BY expression.
2737
2738        Example:
2739            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2740            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2741
2742        Args:
2743            *expressions: the SQL code strings to parse.
2744                If a `Group` instance is passed, this is used as-is.
2745                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2746                If nothing is passed in then a group by is not applied to the expression
2747            append: if `True`, add to any existing expressions.
2748                Otherwise, this flattens all the `Group` expression into a single expression.
2749            dialect: the dialect used to parse the input expression.
2750            copy: if `False`, modify this expression instance in-place.
2751            opts: other options to use to parse the input expressions.
2752
2753        Returns:
2754            The modified Select expression.
2755        """
2756        if not expressions:
2757            return self if not copy else self.copy()
2758
2759        return _apply_child_list_builder(
2760            *expressions,
2761            instance=self,
2762            arg="group",
2763            append=append,
2764            copy=copy,
2765            prefix="GROUP BY",
2766            into=Group,
2767            dialect=dialect,
2768            **opts,
2769        )
2770
2771    def order_by(
2772        self,
2773        *expressions: t.Optional[ExpOrStr],
2774        append: bool = True,
2775        dialect: DialectType = None,
2776        copy: bool = True,
2777        **opts,
2778    ) -> Select:
2779        """
2780        Set the ORDER BY expression.
2781
2782        Example:
2783            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2784            'SELECT x FROM tbl ORDER BY x DESC'
2785
2786        Args:
2787            *expressions: the SQL code strings to parse.
2788                If a `Group` instance is passed, this is used as-is.
2789                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2790            append: if `True`, add to any existing expressions.
2791                Otherwise, this flattens all the `Order` expression into a single expression.
2792            dialect: the dialect used to parse the input expression.
2793            copy: if `False`, modify this expression instance in-place.
2794            opts: other options to use to parse the input expressions.
2795
2796        Returns:
2797            The modified Select expression.
2798        """
2799        return _apply_child_list_builder(
2800            *expressions,
2801            instance=self,
2802            arg="order",
2803            append=append,
2804            copy=copy,
2805            prefix="ORDER BY",
2806            into=Order,
2807            dialect=dialect,
2808            **opts,
2809        )
2810
2811    def sort_by(
2812        self,
2813        *expressions: t.Optional[ExpOrStr],
2814        append: bool = True,
2815        dialect: DialectType = None,
2816        copy: bool = True,
2817        **opts,
2818    ) -> Select:
2819        """
2820        Set the SORT BY expression.
2821
2822        Example:
2823            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2824            'SELECT x FROM tbl SORT BY x DESC'
2825
2826        Args:
2827            *expressions: the SQL code strings to parse.
2828                If a `Group` instance is passed, this is used as-is.
2829                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2830            append: if `True`, add to any existing expressions.
2831                Otherwise, this flattens all the `Order` expression into a single expression.
2832            dialect: the dialect used to parse the input expression.
2833            copy: if `False`, modify this expression instance in-place.
2834            opts: other options to use to parse the input expressions.
2835
2836        Returns:
2837            The modified Select expression.
2838        """
2839        return _apply_child_list_builder(
2840            *expressions,
2841            instance=self,
2842            arg="sort",
2843            append=append,
2844            copy=copy,
2845            prefix="SORT BY",
2846            into=Sort,
2847            dialect=dialect,
2848            **opts,
2849        )
2850
2851    def cluster_by(
2852        self,
2853        *expressions: t.Optional[ExpOrStr],
2854        append: bool = True,
2855        dialect: DialectType = None,
2856        copy: bool = True,
2857        **opts,
2858    ) -> Select:
2859        """
2860        Set the CLUSTER BY expression.
2861
2862        Example:
2863            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2864            'SELECT x FROM tbl CLUSTER BY x DESC'
2865
2866        Args:
2867            *expressions: the SQL code strings to parse.
2868                If a `Group` instance is passed, this is used as-is.
2869                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2870            append: if `True`, add to any existing expressions.
2871                Otherwise, this flattens all the `Order` expression into a single expression.
2872            dialect: the dialect used to parse the input expression.
2873            copy: if `False`, modify this expression instance in-place.
2874            opts: other options to use to parse the input expressions.
2875
2876        Returns:
2877            The modified Select expression.
2878        """
2879        return _apply_child_list_builder(
2880            *expressions,
2881            instance=self,
2882            arg="cluster",
2883            append=append,
2884            copy=copy,
2885            prefix="CLUSTER BY",
2886            into=Cluster,
2887            dialect=dialect,
2888            **opts,
2889        )
2890
2891    def limit(
2892        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2893    ) -> Select:
2894        """
2895        Set the LIMIT expression.
2896
2897        Example:
2898            >>> Select().from_("tbl").select("x").limit(10).sql()
2899            'SELECT x FROM tbl LIMIT 10'
2900
2901        Args:
2902            expression: the SQL code string to parse.
2903                This can also be an integer.
2904                If a `Limit` instance is passed, this is used as-is.
2905                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2906            dialect: the dialect used to parse the input expression.
2907            copy: if `False`, modify this expression instance in-place.
2908            opts: other options to use to parse the input expressions.
2909
2910        Returns:
2911            Select: the modified expression.
2912        """
2913        return _apply_builder(
2914            expression=expression,
2915            instance=self,
2916            arg="limit",
2917            into=Limit,
2918            prefix="LIMIT",
2919            dialect=dialect,
2920            copy=copy,
2921            into_arg="expression",
2922            **opts,
2923        )
2924
2925    def offset(
2926        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2927    ) -> Select:
2928        """
2929        Set the OFFSET expression.
2930
2931        Example:
2932            >>> Select().from_("tbl").select("x").offset(10).sql()
2933            'SELECT x FROM tbl OFFSET 10'
2934
2935        Args:
2936            expression: the SQL code string to parse.
2937                This can also be an integer.
2938                If a `Offset` instance is passed, this is used as-is.
2939                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2940            dialect: the dialect used to parse the input expression.
2941            copy: if `False`, modify this expression instance in-place.
2942            opts: other options to use to parse the input expressions.
2943
2944        Returns:
2945            The modified Select expression.
2946        """
2947        return _apply_builder(
2948            expression=expression,
2949            instance=self,
2950            arg="offset",
2951            into=Offset,
2952            prefix="OFFSET",
2953            dialect=dialect,
2954            copy=copy,
2955            into_arg="expression",
2956            **opts,
2957        )
2958
2959    def select(
2960        self,
2961        *expressions: t.Optional[ExpOrStr],
2962        append: bool = True,
2963        dialect: DialectType = None,
2964        copy: bool = True,
2965        **opts,
2966    ) -> Select:
2967        """
2968        Append to or set the SELECT expressions.
2969
2970        Example:
2971            >>> Select().select("x", "y").sql()
2972            'SELECT x, y'
2973
2974        Args:
2975            *expressions: the SQL code strings to parse.
2976                If an `Expression` instance is passed, it will be used as-is.
2977            append: if `True`, add to any existing expressions.
2978                Otherwise, this resets the expressions.
2979            dialect: the dialect used to parse the input expressions.
2980            copy: if `False`, modify this expression instance in-place.
2981            opts: other options to use to parse the input expressions.
2982
2983        Returns:
2984            The modified Select expression.
2985        """
2986        return _apply_list_builder(
2987            *expressions,
2988            instance=self,
2989            arg="expressions",
2990            append=append,
2991            dialect=dialect,
2992            copy=copy,
2993            **opts,
2994        )
2995
2996    def lateral(
2997        self,
2998        *expressions: t.Optional[ExpOrStr],
2999        append: bool = True,
3000        dialect: DialectType = None,
3001        copy: bool = True,
3002        **opts,
3003    ) -> Select:
3004        """
3005        Append to or set the LATERAL expressions.
3006
3007        Example:
3008            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3009            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3010
3011        Args:
3012            *expressions: the SQL code strings to parse.
3013                If an `Expression` instance is passed, it will be used as-is.
3014            append: if `True`, add to any existing expressions.
3015                Otherwise, this resets the expressions.
3016            dialect: the dialect used to parse the input expressions.
3017            copy: if `False`, modify this expression instance in-place.
3018            opts: other options to use to parse the input expressions.
3019
3020        Returns:
3021            The modified Select expression.
3022        """
3023        return _apply_list_builder(
3024            *expressions,
3025            instance=self,
3026            arg="laterals",
3027            append=append,
3028            into=Lateral,
3029            prefix="LATERAL VIEW",
3030            dialect=dialect,
3031            copy=copy,
3032            **opts,
3033        )
3034
3035    def join(
3036        self,
3037        expression: ExpOrStr,
3038        on: t.Optional[ExpOrStr] = None,
3039        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3040        append: bool = True,
3041        join_type: t.Optional[str] = None,
3042        join_alias: t.Optional[Identifier | str] = None,
3043        dialect: DialectType = None,
3044        copy: bool = True,
3045        **opts,
3046    ) -> Select:
3047        """
3048        Append to or set the JOIN expressions.
3049
3050        Example:
3051            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3052            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3053
3054            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3055            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3056
3057            Use `join_type` to change the type of join:
3058
3059            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3060            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3061
3062        Args:
3063            expression: the SQL code string to parse.
3064                If an `Expression` instance is passed, it will be used as-is.
3065            on: optionally specify the join "on" criteria as a SQL string.
3066                If an `Expression` instance is passed, it will be used as-is.
3067            using: optionally specify the join "using" criteria as a SQL string.
3068                If an `Expression` instance is passed, it will be used as-is.
3069            append: if `True`, add to any existing expressions.
3070                Otherwise, this resets the expressions.
3071            join_type: if set, alter the parsed join type.
3072            join_alias: an optional alias for the joined source.
3073            dialect: the dialect used to parse the input expressions.
3074            copy: if `False`, modify this expression instance in-place.
3075            opts: other options to use to parse the input expressions.
3076
3077        Returns:
3078            Select: the modified expression.
3079        """
3080        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3081
3082        try:
3083            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3084        except ParseError:
3085            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3086
3087        join = expression if isinstance(expression, Join) else Join(this=expression)
3088
3089        if isinstance(join.this, Select):
3090            join.this.replace(join.this.subquery())
3091
3092        if join_type:
3093            method: t.Optional[Token]
3094            side: t.Optional[Token]
3095            kind: t.Optional[Token]
3096
3097            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3098
3099            if method:
3100                join.set("method", method.text)
3101            if side:
3102                join.set("side", side.text)
3103            if kind:
3104                join.set("kind", kind.text)
3105
3106        if on:
3107            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3108            join.set("on", on)
3109
3110        if using:
3111            join = _apply_list_builder(
3112                *ensure_list(using),
3113                instance=join,
3114                arg="using",
3115                append=append,
3116                copy=copy,
3117                into=Identifier,
3118                **opts,
3119            )
3120
3121        if join_alias:
3122            join.set("this", alias_(join.this, join_alias, table=True))
3123
3124        return _apply_list_builder(
3125            join,
3126            instance=self,
3127            arg="joins",
3128            append=append,
3129            copy=copy,
3130            **opts,
3131        )
3132
3133    def where(
3134        self,
3135        *expressions: t.Optional[ExpOrStr],
3136        append: bool = True,
3137        dialect: DialectType = None,
3138        copy: bool = True,
3139        **opts,
3140    ) -> Select:
3141        """
3142        Append to or set the WHERE expressions.
3143
3144        Example:
3145            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3146            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3147
3148        Args:
3149            *expressions: the SQL code strings to parse.
3150                If an `Expression` instance is passed, it will be used as-is.
3151                Multiple expressions are combined with an AND operator.
3152            append: if `True`, AND the new expressions to any existing expression.
3153                Otherwise, this resets the expression.
3154            dialect: the dialect used to parse the input expressions.
3155            copy: if `False`, modify this expression instance in-place.
3156            opts: other options to use to parse the input expressions.
3157
3158        Returns:
3159            Select: the modified expression.
3160        """
3161        return _apply_conjunction_builder(
3162            *expressions,
3163            instance=self,
3164            arg="where",
3165            append=append,
3166            into=Where,
3167            dialect=dialect,
3168            copy=copy,
3169            **opts,
3170        )
3171
3172    def having(
3173        self,
3174        *expressions: t.Optional[ExpOrStr],
3175        append: bool = True,
3176        dialect: DialectType = None,
3177        copy: bool = True,
3178        **opts,
3179    ) -> Select:
3180        """
3181        Append to or set the HAVING expressions.
3182
3183        Example:
3184            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3185            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3186
3187        Args:
3188            *expressions: the SQL code strings to parse.
3189                If an `Expression` instance is passed, it will be used as-is.
3190                Multiple expressions are combined with an AND operator.
3191            append: if `True`, AND the new expressions to any existing expression.
3192                Otherwise, this resets the expression.
3193            dialect: the dialect used to parse the input expressions.
3194            copy: if `False`, modify this expression instance in-place.
3195            opts: other options to use to parse the input expressions.
3196
3197        Returns:
3198            The modified Select expression.
3199        """
3200        return _apply_conjunction_builder(
3201            *expressions,
3202            instance=self,
3203            arg="having",
3204            append=append,
3205            into=Having,
3206            dialect=dialect,
3207            copy=copy,
3208            **opts,
3209        )
3210
3211    def window(
3212        self,
3213        *expressions: t.Optional[ExpOrStr],
3214        append: bool = True,
3215        dialect: DialectType = None,
3216        copy: bool = True,
3217        **opts,
3218    ) -> Select:
3219        return _apply_list_builder(
3220            *expressions,
3221            instance=self,
3222            arg="windows",
3223            append=append,
3224            into=Window,
3225            dialect=dialect,
3226            copy=copy,
3227            **opts,
3228        )
3229
3230    def qualify(
3231        self,
3232        *expressions: t.Optional[ExpOrStr],
3233        append: bool = True,
3234        dialect: DialectType = None,
3235        copy: bool = True,
3236        **opts,
3237    ) -> Select:
3238        return _apply_conjunction_builder(
3239            *expressions,
3240            instance=self,
3241            arg="qualify",
3242            append=append,
3243            into=Qualify,
3244            dialect=dialect,
3245            copy=copy,
3246            **opts,
3247        )
3248
3249    def distinct(
3250        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3251    ) -> Select:
3252        """
3253        Set the OFFSET expression.
3254
3255        Example:
3256            >>> Select().from_("tbl").select("x").distinct().sql()
3257            'SELECT DISTINCT x FROM tbl'
3258
3259        Args:
3260            ons: the expressions to distinct on
3261            distinct: whether the Select should be distinct
3262            copy: if `False`, modify this expression instance in-place.
3263
3264        Returns:
3265            Select: the modified expression.
3266        """
3267        instance = maybe_copy(self, copy)
3268        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3269        instance.set("distinct", Distinct(on=on) if distinct else None)
3270        return instance
3271
3272    def ctas(
3273        self,
3274        table: ExpOrStr,
3275        properties: t.Optional[t.Dict] = None,
3276        dialect: DialectType = None,
3277        copy: bool = True,
3278        **opts,
3279    ) -> Create:
3280        """
3281        Convert this expression to a CREATE TABLE AS statement.
3282
3283        Example:
3284            >>> Select().select("*").from_("tbl").ctas("x").sql()
3285            'CREATE TABLE x AS SELECT * FROM tbl'
3286
3287        Args:
3288            table: the SQL code string to parse as the table name.
3289                If another `Expression` instance is passed, it will be used as-is.
3290            properties: an optional mapping of table properties
3291            dialect: the dialect used to parse the input table.
3292            copy: if `False`, modify this expression instance in-place.
3293            opts: other options to use to parse the input table.
3294
3295        Returns:
3296            The new Create expression.
3297        """
3298        instance = maybe_copy(self, copy)
3299        table_expression = maybe_parse(
3300            table,
3301            into=Table,
3302            dialect=dialect,
3303            **opts,
3304        )
3305        properties_expression = None
3306        if properties:
3307            properties_expression = Properties.from_dict(properties)
3308
3309        return Create(
3310            this=table_expression,
3311            kind="table",
3312            expression=instance,
3313            properties=properties_expression,
3314        )
3315
3316    def lock(self, update: bool = True, copy: bool = True) -> Select:
3317        """
3318        Set the locking read mode for this expression.
3319
3320        Examples:
3321            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3322            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3323
3324            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3325            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3326
3327        Args:
3328            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3329            copy: if `False`, modify this expression instance in-place.
3330
3331        Returns:
3332            The modified expression.
3333        """
3334        inst = maybe_copy(self, copy)
3335        inst.set("locks", [Lock(update=update)])
3336
3337        return inst
3338
3339    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3340        """
3341        Set hints for this expression.
3342
3343        Examples:
3344            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3345            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3346
3347        Args:
3348            hints: The SQL code strings to parse as the hints.
3349                If an `Expression` instance is passed, it will be used as-is.
3350            dialect: The dialect used to parse the hints.
3351            copy: If `False`, modify this expression instance in-place.
3352
3353        Returns:
3354            The modified expression.
3355        """
3356        inst = maybe_copy(self, copy)
3357        inst.set(
3358            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3359        )
3360
3361        return inst
3362
3363    @property
3364    def named_selects(self) -> t.List[str]:
3365        return [e.output_name for e in self.expressions if e.alias_or_name]
3366
3367    @property
3368    def is_star(self) -> bool:
3369        return any(expression.is_star for expression in self.expressions)
3370
3371    @property
3372    def selects(self) -> t.List[Expression]:
3373        return self.expressions
3374
3375
3376class Subquery(DerivedTable, Unionable):
3377    arg_types = {
3378        "this": True,
3379        "alias": False,
3380        "with": False,
3381        **QUERY_MODIFIERS,
3382    }
3383
3384    def unnest(self):
3385        """
3386        Returns the first non subquery.
3387        """
3388        expression = self
3389        while isinstance(expression, Subquery):
3390            expression = expression.this
3391        return expression
3392
3393    def unwrap(self) -> Subquery:
3394        expression = self
3395        while expression.same_parent and expression.is_wrapper:
3396            expression = t.cast(Subquery, expression.parent)
3397        return expression
3398
3399    @property
3400    def is_wrapper(self) -> bool:
3401        """
3402        Whether this Subquery acts as a simple wrapper around another expression.
3403
3404        SELECT * FROM (((SELECT * FROM t)))
3405                      ^
3406                      This corresponds to a "wrapper" Subquery node
3407        """
3408        return all(v is None for k, v in self.args.items() if k != "this")
3409
3410    @property
3411    def is_star(self) -> bool:
3412        return self.this.is_star
3413
3414    @property
3415    def output_name(self) -> str:
3416        return self.alias
3417
3418
3419class TableSample(Expression):
3420    arg_types = {
3421        "this": False,
3422        "expressions": False,
3423        "method": False,
3424        "bucket_numerator": False,
3425        "bucket_denominator": False,
3426        "bucket_field": False,
3427        "percent": False,
3428        "rows": False,
3429        "size": False,
3430        "seed": False,
3431        "kind": False,
3432    }
3433
3434
3435class Tag(Expression):
3436    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3437
3438    arg_types = {
3439        "this": False,
3440        "prefix": False,
3441        "postfix": False,
3442    }
3443
3444
3445# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3446# https://duckdb.org/docs/sql/statements/pivot
3447class Pivot(Expression):
3448    arg_types = {
3449        "this": False,
3450        "alias": False,
3451        "expressions": False,
3452        "field": False,
3453        "unpivot": False,
3454        "using": False,
3455        "group": False,
3456        "columns": False,
3457        "include_nulls": False,
3458    }
3459
3460
3461class Window(Condition):
3462    arg_types = {
3463        "this": True,
3464        "partition_by": False,
3465        "order": False,
3466        "spec": False,
3467        "alias": False,
3468        "over": False,
3469        "first": False,
3470    }
3471
3472
3473class WindowSpec(Expression):
3474    arg_types = {
3475        "kind": False,
3476        "start": False,
3477        "start_side": False,
3478        "end": False,
3479        "end_side": False,
3480    }
3481
3482
3483class Where(Expression):
3484    pass
3485
3486
3487class Star(Expression):
3488    arg_types = {"except": False, "replace": False}
3489
3490    @property
3491    def name(self) -> str:
3492        return "*"
3493
3494    @property
3495    def output_name(self) -> str:
3496        return self.name
3497
3498
3499class Parameter(Condition):
3500    arg_types = {"this": True, "wrapped": False}
3501
3502
3503class SessionParameter(Condition):
3504    arg_types = {"this": True, "kind": False}
3505
3506
3507class Placeholder(Condition):
3508    arg_types = {"this": False, "kind": False}
3509
3510
3511class Null(Condition):
3512    arg_types: t.Dict[str, t.Any] = {}
3513
3514    @property
3515    def name(self) -> str:
3516        return "NULL"
3517
3518
3519class Boolean(Condition):
3520    pass
3521
3522
3523class DataTypeParam(Expression):
3524    arg_types = {"this": True, "expression": False}
3525
3526
3527class DataType(Expression):
3528    arg_types = {
3529        "this": True,
3530        "expressions": False,
3531        "nested": False,
3532        "values": False,
3533        "prefix": False,
3534        "kind": False,
3535    }
3536
3537    class Type(AutoName):
3538        ARRAY = auto()
3539        BIGDECIMAL = auto()
3540        BIGINT = auto()
3541        BIGSERIAL = auto()
3542        BINARY = auto()
3543        BIT = auto()
3544        BOOLEAN = auto()
3545        CHAR = auto()
3546        DATE = auto()
3547        DATEMULTIRANGE = auto()
3548        DATERANGE = auto()
3549        DATETIME = auto()
3550        DATETIME64 = auto()
3551        DECIMAL = auto()
3552        DOUBLE = auto()
3553        ENUM = auto()
3554        ENUM8 = auto()
3555        ENUM16 = auto()
3556        FIXEDSTRING = auto()
3557        FLOAT = auto()
3558        GEOGRAPHY = auto()
3559        GEOMETRY = auto()
3560        HLLSKETCH = auto()
3561        HSTORE = auto()
3562        IMAGE = auto()
3563        INET = auto()
3564        INT = auto()
3565        INT128 = auto()
3566        INT256 = auto()
3567        INT4MULTIRANGE = auto()
3568        INT4RANGE = auto()
3569        INT8MULTIRANGE = auto()
3570        INT8RANGE = auto()
3571        INTERVAL = auto()
3572        IPADDRESS = auto()
3573        IPPREFIX = auto()
3574        JSON = auto()
3575        JSONB = auto()
3576        LONGBLOB = auto()
3577        LONGTEXT = auto()
3578        LOWCARDINALITY = auto()
3579        MAP = auto()
3580        MEDIUMBLOB = auto()
3581        MEDIUMINT = auto()
3582        MEDIUMTEXT = auto()
3583        MONEY = auto()
3584        NCHAR = auto()
3585        NESTED = auto()
3586        NULL = auto()
3587        NULLABLE = auto()
3588        NUMMULTIRANGE = auto()
3589        NUMRANGE = auto()
3590        NVARCHAR = auto()
3591        OBJECT = auto()
3592        ROWVERSION = auto()
3593        SERIAL = auto()
3594        SET = auto()
3595        SMALLINT = auto()
3596        SMALLMONEY = auto()
3597        SMALLSERIAL = auto()
3598        STRUCT = auto()
3599        SUPER = auto()
3600        TEXT = auto()
3601        TINYBLOB = auto()
3602        TINYTEXT = auto()
3603        TIME = auto()
3604        TIMETZ = auto()
3605        TIMESTAMP = auto()
3606        TIMESTAMPLTZ = auto()
3607        TIMESTAMPTZ = auto()
3608        TIMESTAMP_S = auto()
3609        TIMESTAMP_MS = auto()
3610        TIMESTAMP_NS = auto()
3611        TINYINT = auto()
3612        TSMULTIRANGE = auto()
3613        TSRANGE = auto()
3614        TSTZMULTIRANGE = auto()
3615        TSTZRANGE = auto()
3616        UBIGINT = auto()
3617        UINT = auto()
3618        UINT128 = auto()
3619        UINT256 = auto()
3620        UMEDIUMINT = auto()
3621        UDECIMAL = auto()
3622        UNIQUEIDENTIFIER = auto()
3623        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3624        USERDEFINED = "USER-DEFINED"
3625        USMALLINT = auto()
3626        UTINYINT = auto()
3627        UUID = auto()
3628        VARBINARY = auto()
3629        VARCHAR = auto()
3630        VARIANT = auto()
3631        XML = auto()
3632        YEAR = auto()
3633
3634    TEXT_TYPES = {
3635        Type.CHAR,
3636        Type.NCHAR,
3637        Type.VARCHAR,
3638        Type.NVARCHAR,
3639        Type.TEXT,
3640    }
3641
3642    INTEGER_TYPES = {
3643        Type.INT,
3644        Type.TINYINT,
3645        Type.SMALLINT,
3646        Type.BIGINT,
3647        Type.INT128,
3648        Type.INT256,
3649    }
3650
3651    FLOAT_TYPES = {
3652        Type.FLOAT,
3653        Type.DOUBLE,
3654    }
3655
3656    NUMERIC_TYPES = {
3657        *INTEGER_TYPES,
3658        *FLOAT_TYPES,
3659    }
3660
3661    TEMPORAL_TYPES = {
3662        Type.TIME,
3663        Type.TIMETZ,
3664        Type.TIMESTAMP,
3665        Type.TIMESTAMPTZ,
3666        Type.TIMESTAMPLTZ,
3667        Type.TIMESTAMP_S,
3668        Type.TIMESTAMP_MS,
3669        Type.TIMESTAMP_NS,
3670        Type.DATE,
3671        Type.DATETIME,
3672        Type.DATETIME64,
3673    }
3674
3675    @classmethod
3676    def build(
3677        cls,
3678        dtype: str | DataType | DataType.Type,
3679        dialect: DialectType = None,
3680        udt: bool = False,
3681        **kwargs,
3682    ) -> DataType:
3683        """
3684        Constructs a DataType object.
3685
3686        Args:
3687            dtype: the data type of interest.
3688            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3689            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3690                DataType, thus creating a user-defined type.
3691            kawrgs: additional arguments to pass in the constructor of DataType.
3692
3693        Returns:
3694            The constructed DataType object.
3695        """
3696        from sqlglot import parse_one
3697
3698        if isinstance(dtype, str):
3699            if dtype.upper() == "UNKNOWN":
3700                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3701
3702            try:
3703                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
3704            except ParseError:
3705                if udt:
3706                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3707                raise
3708        elif isinstance(dtype, DataType.Type):
3709            data_type_exp = DataType(this=dtype)
3710        elif isinstance(dtype, DataType):
3711            return dtype
3712        else:
3713            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3714
3715        return DataType(**{**data_type_exp.args, **kwargs})
3716
3717    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3718        """
3719        Checks whether this DataType matches one of the provided data types. Nested types or precision
3720        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3721
3722        Args:
3723            dtypes: the data types to compare this DataType to.
3724
3725        Returns:
3726            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3727        """
3728        for dtype in dtypes:
3729            other = DataType.build(dtype, udt=True)
3730
3731            if (
3732                other.expressions
3733                or self.this == DataType.Type.USERDEFINED
3734                or other.this == DataType.Type.USERDEFINED
3735            ):
3736                matches = self == other
3737            else:
3738                matches = self.this == other.this
3739
3740            if matches:
3741                return True
3742        return False
3743
3744
3745# https://www.postgresql.org/docs/15/datatype-pseudo.html
3746class PseudoType(DataType):
3747    arg_types = {"this": True}
3748
3749
3750# https://www.postgresql.org/docs/15/datatype-oid.html
3751class ObjectIdentifier(DataType):
3752    arg_types = {"this": True}
3753
3754
3755# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3756class SubqueryPredicate(Predicate):
3757    pass
3758
3759
3760class All(SubqueryPredicate):
3761    pass
3762
3763
3764class Any(SubqueryPredicate):
3765    pass
3766
3767
3768class Exists(SubqueryPredicate):
3769    pass
3770
3771
3772# Commands to interact with the databases or engines. For most of the command
3773# expressions we parse whatever comes after the command's name as a string.
3774class Command(Expression):
3775    arg_types = {"this": True, "expression": False}
3776
3777
3778class Transaction(Expression):
3779    arg_types = {"this": False, "modes": False, "mark": False}
3780
3781
3782class Commit(Expression):
3783    arg_types = {"chain": False, "this": False, "durability": False}
3784
3785
3786class Rollback(Expression):
3787    arg_types = {"savepoint": False, "this": False}
3788
3789
3790class AlterTable(Expression):
3791    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3792
3793
3794class AddConstraint(Expression):
3795    arg_types = {"this": False, "expression": False, "enforced": False}
3796
3797
3798class DropPartition(Expression):
3799    arg_types = {"expressions": True, "exists": False}
3800
3801
3802# Binary expressions like (ADD a b)
3803class Binary(Condition):
3804    arg_types = {"this": True, "expression": True}
3805
3806    @property
3807    def left(self):
3808        return self.this
3809
3810    @property
3811    def right(self):
3812        return self.expression
3813
3814
3815class Add(Binary):
3816    pass
3817
3818
3819class Connector(Binary):
3820    pass
3821
3822
3823class And(Connector):
3824    pass
3825
3826
3827class Or(Connector):
3828    pass
3829
3830
3831class BitwiseAnd(Binary):
3832    pass
3833
3834
3835class BitwiseLeftShift(Binary):
3836    pass
3837
3838
3839class BitwiseOr(Binary):
3840    pass
3841
3842
3843class BitwiseRightShift(Binary):
3844    pass
3845
3846
3847class BitwiseXor(Binary):
3848    pass
3849
3850
3851class Div(Binary):
3852    pass
3853
3854
3855class Overlaps(Binary):
3856    pass
3857
3858
3859class Dot(Binary):
3860    @property
3861    def name(self) -> str:
3862        return self.expression.name
3863
3864    @property
3865    def output_name(self) -> str:
3866        return self.name
3867
3868    @classmethod
3869    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3870        """Build a Dot object with a sequence of expressions."""
3871        if len(expressions) < 2:
3872            raise ValueError(f"Dot requires >= 2 expressions.")
3873
3874        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3875
3876
3877class DPipe(Binary):
3878    pass
3879
3880
3881class SafeDPipe(DPipe):
3882    pass
3883
3884
3885class EQ(Binary, Predicate):
3886    pass
3887
3888
3889class NullSafeEQ(Binary, Predicate):
3890    pass
3891
3892
3893class NullSafeNEQ(Binary, Predicate):
3894    pass
3895
3896
3897class Distance(Binary):
3898    pass
3899
3900
3901class Escape(Binary):
3902    pass
3903
3904
3905class Glob(Binary, Predicate):
3906    pass
3907
3908
3909class GT(Binary, Predicate):
3910    pass
3911
3912
3913class GTE(Binary, Predicate):
3914    pass
3915
3916
3917class ILike(Binary, Predicate):
3918    pass
3919
3920
3921class ILikeAny(Binary, Predicate):
3922    pass
3923
3924
3925class IntDiv(Binary):
3926    pass
3927
3928
3929class Is(Binary, Predicate):
3930    pass
3931
3932
3933class Kwarg(Binary):
3934    """Kwarg in special functions like func(kwarg => y)."""
3935
3936
3937class Like(Binary, Predicate):
3938    pass
3939
3940
3941class LikeAny(Binary, Predicate):
3942    pass
3943
3944
3945class LT(Binary, Predicate):
3946    pass
3947
3948
3949class LTE(Binary, Predicate):
3950    pass
3951
3952
3953class Mod(Binary):
3954    pass
3955
3956
3957class Mul(Binary):
3958    pass
3959
3960
3961class NEQ(Binary, Predicate):
3962    pass
3963
3964
3965class SimilarTo(Binary, Predicate):
3966    pass
3967
3968
3969class Slice(Binary):
3970    arg_types = {"this": False, "expression": False}
3971
3972
3973class Sub(Binary):
3974    pass
3975
3976
3977class ArrayOverlaps(Binary):
3978    pass
3979
3980
3981# Unary Expressions
3982# (NOT a)
3983class Unary(Condition):
3984    pass
3985
3986
3987class BitwiseNot(Unary):
3988    pass
3989
3990
3991class Not(Unary):
3992    pass
3993
3994
3995class Paren(Unary):
3996    arg_types = {"this": True, "with": False}
3997
3998    @property
3999    def output_name(self) -> str:
4000        return self.this.name
4001
4002
4003class Neg(Unary):
4004    pass
4005
4006
4007class Alias(Expression):
4008    arg_types = {"this": True, "alias": False}
4009
4010    @property
4011    def output_name(self) -> str:
4012        return self.alias
4013
4014
4015class Aliases(Expression):
4016    arg_types = {"this": True, "expressions": True}
4017
4018    @property
4019    def aliases(self):
4020        return self.expressions
4021
4022
4023class AtTimeZone(Expression):
4024    arg_types = {"this": True, "zone": True}
4025
4026
4027class Between(Predicate):
4028    arg_types = {"this": True, "low": True, "high": True}
4029
4030
4031class Bracket(Condition):
4032    arg_types = {"this": True, "expressions": True}
4033
4034    @property
4035    def output_name(self) -> str:
4036        if len(self.expressions) == 1:
4037            return self.expressions[0].output_name
4038
4039        return super().output_name
4040
4041
4042class SafeBracket(Bracket):
4043    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4044
4045
4046class Distinct(Expression):
4047    arg_types = {"expressions": False, "on": False}
4048
4049
4050class In(Predicate):
4051    arg_types = {
4052        "this": True,
4053        "expressions": False,
4054        "query": False,
4055        "unnest": False,
4056        "field": False,
4057        "is_global": False,
4058    }
4059
4060
4061class TimeUnit(Expression):
4062    """Automatically converts unit arg into a var."""
4063
4064    arg_types = {"unit": False}
4065
4066    def __init__(self, **args):
4067        unit = args.get("unit")
4068        if isinstance(unit, (Column, Literal)):
4069            args["unit"] = Var(this=unit.name)
4070        elif isinstance(unit, Week):
4071            unit.set("this", Var(this=unit.this.name))
4072
4073        super().__init__(**args)
4074
4075    @property
4076    def unit(self) -> t.Optional[Var]:
4077        return self.args.get("unit")
4078
4079
4080class IntervalOp(TimeUnit):
4081    arg_types = {"unit": True, "expression": True}
4082
4083    def interval(self):
4084        return Interval(
4085            this=self.expression.copy(),
4086            unit=self.unit.copy(),
4087        )
4088
4089
4090# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4091# https://trino.io/docs/current/language/types.html#interval-day-to-second
4092# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4093class IntervalSpan(DataType):
4094    arg_types = {"this": True, "expression": True}
4095
4096
4097class Interval(TimeUnit):
4098    arg_types = {"this": False, "unit": False}
4099
4100
4101class IgnoreNulls(Expression):
4102    pass
4103
4104
4105class RespectNulls(Expression):
4106    pass
4107
4108
4109# Functions
4110class Func(Condition):
4111    """
4112    The base class for all function expressions.
4113
4114    Attributes:
4115        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4116            treated as a variable length argument and the argument's value will be stored as a list.
4117        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4118            for this function expression. These values are used to map this node to a name during parsing
4119            as well as to provide the function's name during SQL string generation. By default the SQL
4120            name is set to the expression's class name transformed to snake case.
4121    """
4122
4123    is_var_len_args = False
4124
4125    @classmethod
4126    def from_arg_list(cls, args):
4127        if cls.is_var_len_args:
4128            all_arg_keys = list(cls.arg_types)
4129            # If this function supports variable length argument treat the last argument as such.
4130            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4131            num_non_var = len(non_var_len_arg_keys)
4132
4133            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4134            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4135        else:
4136            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4137
4138        return cls(**args_dict)
4139
4140    @classmethod
4141    def sql_names(cls):
4142        if cls is Func:
4143            raise NotImplementedError(
4144                "SQL name is only supported by concrete function implementations"
4145            )
4146        if "_sql_names" not in cls.__dict__:
4147            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4148        return cls._sql_names
4149
4150    @classmethod
4151    def sql_name(cls):
4152        return cls.sql_names()[0]
4153
4154    @classmethod
4155    def default_parser_mappings(cls):
4156        return {name: cls.from_arg_list for name in cls.sql_names()}
4157
4158
4159class AggFunc(Func):
4160    pass
4161
4162
4163class ParameterizedAgg(AggFunc):
4164    arg_types = {"this": True, "expressions": True, "params": True}
4165
4166
4167class Abs(Func):
4168    pass
4169
4170
4171# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4172class Transform(Func):
4173    arg_types = {"this": True, "expression": True}
4174
4175
4176class Anonymous(Func):
4177    arg_types = {"this": True, "expressions": False}
4178    is_var_len_args = True
4179
4180
4181# https://docs.snowflake.com/en/sql-reference/functions/hll
4182# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4183class Hll(AggFunc):
4184    arg_types = {"this": True, "expressions": False}
4185    is_var_len_args = True
4186
4187
4188class ApproxDistinct(AggFunc):
4189    arg_types = {"this": True, "accuracy": False}
4190    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4191
4192
4193class Array(Func):
4194    arg_types = {"expressions": False}
4195    is_var_len_args = True
4196
4197
4198# https://docs.snowflake.com/en/sql-reference/functions/to_char
4199class ToChar(Func):
4200    arg_types = {"this": True, "format": False}
4201
4202
4203class GenerateSeries(Func):
4204    arg_types = {"start": True, "end": True, "step": False}
4205
4206
4207class ArrayAgg(AggFunc):
4208    pass
4209
4210
4211class ArrayAll(Func):
4212    arg_types = {"this": True, "expression": True}
4213
4214
4215class ArrayAny(Func):
4216    arg_types = {"this": True, "expression": True}
4217
4218
4219class ArrayConcat(Func):
4220    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4221    arg_types = {"this": True, "expressions": False}
4222    is_var_len_args = True
4223
4224
4225class ArrayContains(Binary, Func):
4226    pass
4227
4228
4229class ArrayContained(Binary):
4230    pass
4231
4232
4233class ArrayFilter(Func):
4234    arg_types = {"this": True, "expression": True}
4235    _sql_names = ["FILTER", "ARRAY_FILTER"]
4236
4237
4238class ArrayJoin(Func):
4239    arg_types = {"this": True, "expression": True, "null": False}
4240
4241
4242class ArraySize(Func):
4243    arg_types = {"this": True, "expression": False}
4244
4245
4246class ArraySort(Func):
4247    arg_types = {"this": True, "expression": False}
4248
4249
4250class ArraySum(Func):
4251    pass
4252
4253
4254class ArrayUnionAgg(AggFunc):
4255    pass
4256
4257
4258class Avg(AggFunc):
4259    pass
4260
4261
4262class AnyValue(AggFunc):
4263    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4264
4265
4266class First(Func):
4267    arg_types = {"this": True, "ignore_nulls": False}
4268
4269
4270class Last(Func):
4271    arg_types = {"this": True, "ignore_nulls": False}
4272
4273
4274class Case(Func):
4275    arg_types = {"this": False, "ifs": True, "default": False}
4276
4277    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4278        instance = maybe_copy(self, copy)
4279        instance.append(
4280            "ifs",
4281            If(
4282                this=maybe_parse(condition, copy=copy, **opts),
4283                true=maybe_parse(then, copy=copy, **opts),
4284            ),
4285        )
4286        return instance
4287
4288    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4289        instance = maybe_copy(self, copy)
4290        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4291        return instance
4292
4293
4294class Cast(Func):
4295    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4296
4297    @property
4298    def name(self) -> str:
4299        return self.this.name
4300
4301    @property
4302    def to(self) -> DataType:
4303        return self.args["to"]
4304
4305    @property
4306    def output_name(self) -> str:
4307        return self.name
4308
4309    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4310        """
4311        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4312        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4313        array<int> != array<float>.
4314
4315        Args:
4316            dtypes: the data types to compare this Cast's DataType to.
4317
4318        Returns:
4319            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4320        """
4321        return self.to.is_type(*dtypes)
4322
4323
4324class TryCast(Cast):
4325    pass
4326
4327
4328class CastToStrType(Func):
4329    arg_types = {"this": True, "to": True}
4330
4331
4332class Collate(Binary, Func):
4333    pass
4334
4335
4336class Ceil(Func):
4337    arg_types = {"this": True, "decimals": False}
4338    _sql_names = ["CEIL", "CEILING"]
4339
4340
4341class Coalesce(Func):
4342    arg_types = {"this": True, "expressions": False}
4343    is_var_len_args = True
4344    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4345
4346
4347class Chr(Func):
4348    arg_types = {"this": True, "charset": False, "expressions": False}
4349    is_var_len_args = True
4350    _sql_names = ["CHR", "CHAR"]
4351
4352
4353class Concat(Func):
4354    arg_types = {"expressions": True}
4355    is_var_len_args = True
4356
4357
4358class SafeConcat(Concat):
4359    pass
4360
4361
4362class ConcatWs(Concat):
4363    _sql_names = ["CONCAT_WS"]
4364
4365
4366class Count(AggFunc):
4367    arg_types = {"this": False, "expressions": False}
4368    is_var_len_args = True
4369
4370
4371class CountIf(AggFunc):
4372    pass
4373
4374
4375class CurrentDate(Func):
4376    arg_types = {"this": False}
4377
4378
4379class CurrentDatetime(Func):
4380    arg_types = {"this": False}
4381
4382
4383class CurrentTime(Func):
4384    arg_types = {"this": False}
4385
4386
4387class CurrentTimestamp(Func):
4388    arg_types = {"this": False}
4389
4390
4391class CurrentUser(Func):
4392    arg_types = {"this": False}
4393
4394
4395class DateAdd(Func, IntervalOp):
4396    arg_types = {"this": True, "expression": True, "unit": False}
4397
4398
4399class DateSub(Func, IntervalOp):
4400    arg_types = {"this": True, "expression": True, "unit": False}
4401
4402
4403class DateDiff(Func, TimeUnit):
4404    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4405    arg_types = {"this": True, "expression": True, "unit": False}
4406
4407
4408class DateTrunc(Func):
4409    arg_types = {"unit": True, "this": True, "zone": False}
4410
4411    @property
4412    def unit(self) -> Expression:
4413        return self.args["unit"]
4414
4415
4416class DatetimeAdd(Func, IntervalOp):
4417    arg_types = {"this": True, "expression": True, "unit": False}
4418
4419
4420class DatetimeSub(Func, IntervalOp):
4421    arg_types = {"this": True, "expression": True, "unit": False}
4422
4423
4424class DatetimeDiff(Func, TimeUnit):
4425    arg_types = {"this": True, "expression": True, "unit": False}
4426
4427
4428class DatetimeTrunc(Func, TimeUnit):
4429    arg_types = {"this": True, "unit": True, "zone": False}
4430
4431
4432class DayOfWeek(Func):
4433    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4434
4435
4436class DayOfMonth(Func):
4437    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4438
4439
4440class DayOfYear(Func):
4441    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4442
4443
4444class ToDays(Func):
4445    pass
4446
4447
4448class WeekOfYear(Func):
4449    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4450
4451
4452class MonthsBetween(Func):
4453    arg_types = {"this": True, "expression": True, "roundoff": False}
4454
4455
4456class LastDateOfMonth(Func):
4457    pass
4458
4459
4460class Extract(Func):
4461    arg_types = {"this": True, "expression": True}
4462
4463
4464class Timestamp(Func):
4465    arg_types = {"this": False, "expression": False}
4466
4467
4468class TimestampAdd(Func, TimeUnit):
4469    arg_types = {"this": True, "expression": True, "unit": False}
4470
4471
4472class TimestampSub(Func, TimeUnit):
4473    arg_types = {"this": True, "expression": True, "unit": False}
4474
4475
4476class TimestampDiff(Func, TimeUnit):
4477    arg_types = {"this": True, "expression": True, "unit": False}
4478
4479
4480class TimestampTrunc(Func, TimeUnit):
4481    arg_types = {"this": True, "unit": True, "zone": False}
4482
4483
4484class TimeAdd(Func, TimeUnit):
4485    arg_types = {"this": True, "expression": True, "unit": False}
4486
4487
4488class TimeSub(Func, TimeUnit):
4489    arg_types = {"this": True, "expression": True, "unit": False}
4490
4491
4492class TimeDiff(Func, TimeUnit):
4493    arg_types = {"this": True, "expression": True, "unit": False}
4494
4495
4496class TimeTrunc(Func, TimeUnit):
4497    arg_types = {"this": True, "unit": True, "zone": False}
4498
4499
4500class DateFromParts(Func):
4501    _sql_names = ["DATEFROMPARTS"]
4502    arg_types = {"year": True, "month": True, "day": True}
4503
4504
4505class DateStrToDate(Func):
4506    pass
4507
4508
4509class DateToDateStr(Func):
4510    pass
4511
4512
4513class DateToDi(Func):
4514    pass
4515
4516
4517# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4518class Date(Func):
4519    arg_types = {"this": False, "zone": False, "expressions": False}
4520    is_var_len_args = True
4521
4522
4523class Day(Func):
4524    pass
4525
4526
4527class Decode(Func):
4528    arg_types = {"this": True, "charset": True, "replace": False}
4529
4530
4531class DiToDate(Func):
4532    pass
4533
4534
4535class Encode(Func):
4536    arg_types = {"this": True, "charset": True}
4537
4538
4539class Exp(Func):
4540    pass
4541
4542
4543class Explode(Func):
4544    pass
4545
4546
4547class ExplodeOuter(Explode):
4548    pass
4549
4550
4551class Posexplode(Explode):
4552    pass
4553
4554
4555class PosexplodeOuter(Posexplode):
4556    pass
4557
4558
4559class Floor(Func):
4560    arg_types = {"this": True, "decimals": False}
4561
4562
4563class FromBase64(Func):
4564    pass
4565
4566
4567class ToBase64(Func):
4568    pass
4569
4570
4571class Greatest(Func):
4572    arg_types = {"this": True, "expressions": False}
4573    is_var_len_args = True
4574
4575
4576class GroupConcat(AggFunc):
4577    arg_types = {"this": True, "separator": False}
4578
4579
4580class Hex(Func):
4581    pass
4582
4583
4584class Xor(Connector, Func):
4585    arg_types = {"this": False, "expression": False, "expressions": False}
4586
4587
4588class If(Func):
4589    arg_types = {"this": True, "true": True, "false": False}
4590
4591
4592class Initcap(Func):
4593    arg_types = {"this": True, "expression": False}
4594
4595
4596class IsNan(Func):
4597    _sql_names = ["IS_NAN", "ISNAN"]
4598
4599
4600class FormatJson(Expression):
4601    pass
4602
4603
4604class JSONKeyValue(Expression):
4605    arg_types = {"this": True, "expression": True}
4606
4607
4608class JSONObject(Func):
4609    arg_types = {
4610        "expressions": False,
4611        "null_handling": False,
4612        "unique_keys": False,
4613        "return_type": False,
4614        "encoding": False,
4615    }
4616
4617
4618# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4619class JSONArray(Func):
4620    arg_types = {
4621        "expressions": True,
4622        "null_handling": False,
4623        "return_type": False,
4624        "strict": False,
4625    }
4626
4627
4628# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4629class JSONArrayAgg(Func):
4630    arg_types = {
4631        "this": True,
4632        "order": False,
4633        "null_handling": False,
4634        "return_type": False,
4635        "strict": False,
4636    }
4637
4638
4639# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4640# Note: parsing of JSON column definitions is currently incomplete.
4641class JSONColumnDef(Expression):
4642    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4643
4644
4645class JSONSchema(Expression):
4646    arg_types = {"expressions": True}
4647
4648
4649# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4650class JSONTable(Func):
4651    arg_types = {
4652        "this": True,
4653        "schema": True,
4654        "path": False,
4655        "error_handling": False,
4656        "empty_handling": False,
4657    }
4658
4659
4660class OpenJSONColumnDef(Expression):
4661    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4662
4663
4664class OpenJSON(Func):
4665    arg_types = {"this": True, "path": False, "expressions": False}
4666
4667
4668class JSONBContains(Binary):
4669    _sql_names = ["JSONB_CONTAINS"]
4670
4671
4672class JSONExtract(Binary, Func):
4673    _sql_names = ["JSON_EXTRACT"]
4674
4675
4676class JSONExtractScalar(JSONExtract):
4677    _sql_names = ["JSON_EXTRACT_SCALAR"]
4678
4679
4680class JSONBExtract(JSONExtract):
4681    _sql_names = ["JSONB_EXTRACT"]
4682
4683
4684class JSONBExtractScalar(JSONExtract):
4685    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4686
4687
4688class JSONFormat(Func):
4689    arg_types = {"this": False, "options": False}
4690    _sql_names = ["JSON_FORMAT"]
4691
4692
4693# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4694class JSONArrayContains(Binary, Predicate, Func):
4695    _sql_names = ["JSON_ARRAY_CONTAINS"]
4696
4697
4698class ParseJSON(Func):
4699    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4700    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4701
4702
4703class Least(Func):
4704    arg_types = {"this": True, "expressions": False}
4705    is_var_len_args = True
4706
4707
4708class Left(Func):
4709    arg_types = {"this": True, "expression": True}
4710
4711
4712class Right(Func):
4713    arg_types = {"this": True, "expression": True}
4714
4715
4716class Length(Func):
4717    _sql_names = ["LENGTH", "LEN"]
4718
4719
4720class Levenshtein(Func):
4721    arg_types = {
4722        "this": True,
4723        "expression": False,
4724        "ins_cost": False,
4725        "del_cost": False,
4726        "sub_cost": False,
4727    }
4728
4729
4730class Ln(Func):
4731    pass
4732
4733
4734class Log(Func):
4735    arg_types = {"this": True, "expression": False}
4736
4737
4738class Log2(Func):
4739    pass
4740
4741
4742class Log10(Func):
4743    pass
4744
4745
4746class LogicalOr(AggFunc):
4747    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4748
4749
4750class LogicalAnd(AggFunc):
4751    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4752
4753
4754class Lower(Func):
4755    _sql_names = ["LOWER", "LCASE"]
4756
4757
4758class Map(Func):
4759    arg_types = {"keys": False, "values": False}
4760
4761
4762class MapFromEntries(Func):
4763    pass
4764
4765
4766class StarMap(Func):
4767    pass
4768
4769
4770class VarMap(Func):
4771    arg_types = {"keys": True, "values": True}
4772    is_var_len_args = True
4773
4774    @property
4775    def keys(self) -> t.List[Expression]:
4776        return self.args["keys"].expressions
4777
4778    @property
4779    def values(self) -> t.List[Expression]:
4780        return self.args["values"].expressions
4781
4782
4783# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4784class MatchAgainst(Func):
4785    arg_types = {"this": True, "expressions": True, "modifier": False}
4786
4787
4788class Max(AggFunc):
4789    arg_types = {"this": True, "expressions": False}
4790    is_var_len_args = True
4791
4792
4793class MD5(Func):
4794    _sql_names = ["MD5"]
4795
4796
4797# Represents the variant of the MD5 function that returns a binary value
4798class MD5Digest(Func):
4799    _sql_names = ["MD5_DIGEST"]
4800
4801
4802class Min(AggFunc):
4803    arg_types = {"this": True, "expressions": False}
4804    is_var_len_args = True
4805
4806
4807class Month(Func):
4808    pass
4809
4810
4811class Nvl2(Func):
4812    arg_types = {"this": True, "true": True, "false": False}
4813
4814
4815# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4816class Predict(Func):
4817    arg_types = {"this": True, "expression": True, "params_struct": False}
4818
4819
4820class Pow(Binary, Func):
4821    _sql_names = ["POWER", "POW"]
4822
4823
4824class PercentileCont(AggFunc):
4825    arg_types = {"this": True, "expression": False}
4826
4827
4828class PercentileDisc(AggFunc):
4829    arg_types = {"this": True, "expression": False}
4830
4831
4832class Quantile(AggFunc):
4833    arg_types = {"this": True, "quantile": True}
4834
4835
4836class ApproxQuantile(Quantile):
4837    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4838
4839
4840class RangeN(Func):
4841    arg_types = {"this": True, "expressions": True, "each": False}
4842
4843
4844class ReadCSV(Func):
4845    _sql_names = ["READ_CSV"]
4846    is_var_len_args = True
4847    arg_types = {"this": True, "expressions": False}
4848
4849
4850class Reduce(Func):
4851    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4852
4853
4854class RegexpExtract(Func):
4855    arg_types = {
4856        "this": True,
4857        "expression": True,
4858        "position": False,
4859        "occurrence": False,
4860        "parameters": False,
4861        "group": False,
4862    }
4863
4864
4865class RegexpReplace(Func):
4866    arg_types = {
4867        "this": True,
4868        "expression": True,
4869        "replacement": True,
4870        "position": False,
4871        "occurrence": False,
4872        "parameters": False,
4873    }
4874
4875
4876class RegexpLike(Binary, Func):
4877    arg_types = {"this": True, "expression": True, "flag": False}
4878
4879
4880class RegexpILike(Func):
4881    arg_types = {"this": True, "expression": True, "flag": False}
4882
4883
4884# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4885# limit is the number of times a pattern is applied
4886class RegexpSplit(Func):
4887    arg_types = {"this": True, "expression": True, "limit": False}
4888
4889
4890class Repeat(Func):
4891    arg_types = {"this": True, "times": True}
4892
4893
4894class Round(Func):
4895    arg_types = {"this": True, "decimals": False}
4896
4897
4898class RowNumber(Func):
4899    arg_types: t.Dict[str, t.Any] = {}
4900
4901
4902class SafeDivide(Func):
4903    arg_types = {"this": True, "expression": True}
4904
4905
4906class SetAgg(AggFunc):
4907    pass
4908
4909
4910class SHA(Func):
4911    _sql_names = ["SHA", "SHA1"]
4912
4913
4914class SHA2(Func):
4915    _sql_names = ["SHA2"]
4916    arg_types = {"this": True, "length": False}
4917
4918
4919class SortArray(Func):
4920    arg_types = {"this": True, "asc": False}
4921
4922
4923class Split(Func):
4924    arg_types = {"this": True, "expression": True, "limit": False}
4925
4926
4927# Start may be omitted in the case of postgres
4928# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4929class Substring(Func):
4930    arg_types = {"this": True, "start": False, "length": False}
4931
4932
4933class StandardHash(Func):
4934    arg_types = {"this": True, "expression": False}
4935
4936
4937class StartsWith(Func):
4938    _sql_names = ["STARTS_WITH", "STARTSWITH"]
4939    arg_types = {"this": True, "expression": True}
4940
4941
4942class StrPosition(Func):
4943    arg_types = {
4944        "this": True,
4945        "substr": True,
4946        "position": False,
4947        "instance": False,
4948    }
4949
4950
4951class StrToDate(Func):
4952    arg_types = {"this": True, "format": True}
4953
4954
4955class StrToTime(Func):
4956    arg_types = {"this": True, "format": True, "zone": False}
4957
4958
4959# Spark allows unix_timestamp()
4960# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
4961class StrToUnix(Func):
4962    arg_types = {"this": False, "format": False}
4963
4964
4965# https://prestodb.io/docs/current/functions/string.html
4966# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
4967class StrToMap(Func):
4968    arg_types = {
4969        "this": True,
4970        "pair_delim": False,
4971        "key_value_delim": False,
4972        "duplicate_resolution_callback": False,
4973    }
4974
4975
4976class NumberToStr(Func):
4977    arg_types = {"this": True, "format": True, "culture": False}
4978
4979
4980class FromBase(Func):
4981    arg_types = {"this": True, "expression": True}
4982
4983
4984class Struct(Func):
4985    arg_types = {"expressions": True}
4986    is_var_len_args = True
4987
4988
4989class StructExtract(Func):
4990    arg_types = {"this": True, "expression": True}
4991
4992
4993# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
4994# https://docs.snowflake.com/en/sql-reference/functions/insert
4995class Stuff(Func):
4996    _sql_names = ["STUFF", "INSERT"]
4997    arg_types = {"this": True, "start": True, "length": True, "expression": True}
4998
4999
5000class Sum(AggFunc):
5001    pass
5002
5003
5004class Sqrt(Func):
5005    pass
5006
5007
5008class Stddev(AggFunc):
5009    pass
5010
5011
5012class StddevPop(AggFunc):
5013    pass
5014
5015
5016class StddevSamp(AggFunc):
5017    pass
5018
5019
5020class TimeToStr(Func):
5021    arg_types = {"this": True, "format": True, "culture": False}
5022
5023
5024class TimeToTimeStr(Func):
5025    pass
5026
5027
5028class TimeToUnix(Func):
5029    pass
5030
5031
5032class TimeStrToDate(Func):
5033    pass
5034
5035
5036class TimeStrToTime(Func):
5037    pass
5038
5039
5040class TimeStrToUnix(Func):
5041    pass
5042
5043
5044class Trim(Func):
5045    arg_types = {
5046        "this": True,
5047        "expression": False,
5048        "position": False,
5049        "collation": False,
5050    }
5051
5052
5053class TsOrDsAdd(Func, TimeUnit):
5054    arg_types = {"this": True, "expression": True, "unit": False}
5055
5056
5057class TsOrDsToDateStr(Func):
5058    pass
5059
5060
5061class TsOrDsToDate(Func):
5062    arg_types = {"this": True, "format": False}
5063
5064
5065class TsOrDiToDi(Func):
5066    pass
5067
5068
5069class Unhex(Func):
5070    pass
5071
5072
5073class UnixToStr(Func):
5074    arg_types = {"this": True, "format": False}
5075
5076
5077# https://prestodb.io/docs/current/functions/datetime.html
5078# presto has weird zone/hours/minutes
5079class UnixToTime(Func):
5080    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5081
5082    SECONDS = Literal.string("seconds")
5083    MILLIS = Literal.string("millis")
5084    MICROS = Literal.string("micros")
5085
5086
5087class UnixToTimeStr(Func):
5088    pass
5089
5090
5091class Upper(Func):
5092    _sql_names = ["UPPER", "UCASE"]
5093
5094
5095class Variance(AggFunc):
5096    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5097
5098
5099class VariancePop(AggFunc):
5100    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5101
5102
5103class Week(Func):
5104    arg_types = {"this": True, "mode": False}
5105
5106
5107class XMLTable(Func):
5108    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5109
5110
5111class Year(Func):
5112    pass
5113
5114
5115class Use(Expression):
5116    arg_types = {"this": True, "kind": False}
5117
5118
5119class Merge(Expression):
5120    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
5121
5122
5123class When(Func):
5124    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5125
5126
5127# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5128# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5129class NextValueFor(Func):
5130    arg_types = {"this": True, "order": False}
5131
5132
5133def _norm_arg(arg):
5134    return arg.lower() if type(arg) is str else arg
5135
5136
5137ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5138
5139
5140# Helpers
5141@t.overload
5142def maybe_parse(
5143    sql_or_expression: ExpOrStr,
5144    *,
5145    into: t.Type[E],
5146    dialect: DialectType = None,
5147    prefix: t.Optional[str] = None,
5148    copy: bool = False,
5149    **opts,
5150) -> E:
5151    ...
5152
5153
5154@t.overload
5155def maybe_parse(
5156    sql_or_expression: str | E,
5157    *,
5158    into: t.Optional[IntoType] = None,
5159    dialect: DialectType = None,
5160    prefix: t.Optional[str] = None,
5161    copy: bool = False,
5162    **opts,
5163) -> E:
5164    ...
5165
5166
5167def maybe_parse(
5168    sql_or_expression: ExpOrStr,
5169    *,
5170    into: t.Optional[IntoType] = None,
5171    dialect: DialectType = None,
5172    prefix: t.Optional[str] = None,
5173    copy: bool = False,
5174    **opts,
5175) -> Expression:
5176    """Gracefully handle a possible string or expression.
5177
5178    Example:
5179        >>> maybe_parse("1")
5180        (LITERAL this: 1, is_string: False)
5181        >>> maybe_parse(to_identifier("x"))
5182        (IDENTIFIER this: x, quoted: False)
5183
5184    Args:
5185        sql_or_expression: the SQL code string or an expression
5186        into: the SQLGlot Expression to parse into
5187        dialect: the dialect used to parse the input expressions (in the case that an
5188            input expression is a SQL string).
5189        prefix: a string to prefix the sql with before it gets parsed
5190            (automatically includes a space)
5191        copy: whether or not to copy the expression.
5192        **opts: other options to use to parse the input expressions (again, in the case
5193            that an input expression is a SQL string).
5194
5195    Returns:
5196        Expression: the parsed or given expression.
5197    """
5198    if isinstance(sql_or_expression, Expression):
5199        if copy:
5200            return sql_or_expression.copy()
5201        return sql_or_expression
5202
5203    if sql_or_expression is None:
5204        raise ParseError(f"SQL cannot be None")
5205
5206    import sqlglot
5207
5208    sql = str(sql_or_expression)
5209    if prefix:
5210        sql = f"{prefix} {sql}"
5211
5212    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5213
5214
5215@t.overload
5216def maybe_copy(instance: None, copy: bool = True) -> None:
5217    ...
5218
5219
5220@t.overload
5221def maybe_copy(instance: E, copy: bool = True) -> E:
5222    ...
5223
5224
5225def maybe_copy(instance, copy=True):
5226    return instance.copy() if copy and instance else instance
5227
5228
5229def _is_wrong_expression(expression, into):
5230    return isinstance(expression, Expression) and not isinstance(expression, into)
5231
5232
5233def _apply_builder(
5234    expression,
5235    instance,
5236    arg,
5237    copy=True,
5238    prefix=None,
5239    into=None,
5240    dialect=None,
5241    into_arg="this",
5242    **opts,
5243):
5244    if _is_wrong_expression(expression, into):
5245        expression = into(**{into_arg: expression})
5246    instance = maybe_copy(instance, copy)
5247    expression = maybe_parse(
5248        sql_or_expression=expression,
5249        prefix=prefix,
5250        into=into,
5251        dialect=dialect,
5252        **opts,
5253    )
5254    instance.set(arg, expression)
5255    return instance
5256
5257
5258def _apply_child_list_builder(
5259    *expressions,
5260    instance,
5261    arg,
5262    append=True,
5263    copy=True,
5264    prefix=None,
5265    into=None,
5266    dialect=None,
5267    properties=None,
5268    **opts,
5269):
5270    instance = maybe_copy(instance, copy)
5271    parsed = []
5272    for expression in expressions:
5273        if expression is not None:
5274            if _is_wrong_expression(expression, into):
5275                expression = into(expressions=[expression])
5276
5277            expression = maybe_parse(
5278                expression,
5279                into=into,
5280                dialect=dialect,
5281                prefix=prefix,
5282                **opts,
5283            )
5284            parsed.extend(expression.expressions)
5285
5286    existing = instance.args.get(arg)
5287    if append and existing:
5288        parsed = existing.expressions + parsed
5289
5290    child = into(expressions=parsed)
5291    for k, v in (properties or {}).items():
5292        child.set(k, v)
5293    instance.set(arg, child)
5294
5295    return instance
5296
5297
5298def _apply_list_builder(
5299    *expressions,
5300    instance,
5301    arg,
5302    append=True,
5303    copy=True,
5304    prefix=None,
5305    into=None,
5306    dialect=None,
5307    **opts,
5308):
5309    inst = maybe_copy(instance, copy)
5310
5311    expressions = [
5312        maybe_parse(
5313            sql_or_expression=expression,
5314            into=into,
5315            prefix=prefix,
5316            dialect=dialect,
5317            **opts,
5318        )
5319        for expression in expressions
5320        if expression is not None
5321    ]
5322
5323    existing_expressions = inst.args.get(arg)
5324    if append and existing_expressions:
5325        expressions = existing_expressions + expressions
5326
5327    inst.set(arg, expressions)
5328    return inst
5329
5330
5331def _apply_conjunction_builder(
5332    *expressions,
5333    instance,
5334    arg,
5335    into=None,
5336    append=True,
5337    copy=True,
5338    dialect=None,
5339    **opts,
5340):
5341    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5342    if not expressions:
5343        return instance
5344
5345    inst = maybe_copy(instance, copy)
5346
5347    existing = inst.args.get(arg)
5348    if append and existing is not None:
5349        expressions = [existing.this if into else existing] + list(expressions)
5350
5351    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5352
5353    inst.set(arg, into(this=node) if into else node)
5354    return inst
5355
5356
5357def _apply_cte_builder(
5358    instance: E,
5359    alias: ExpOrStr,
5360    as_: ExpOrStr,
5361    recursive: t.Optional[bool] = None,
5362    append: bool = True,
5363    dialect: DialectType = None,
5364    copy: bool = True,
5365    **opts,
5366) -> E:
5367    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5368    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5369    cte = CTE(this=as_expression, alias=alias_expression)
5370    return _apply_child_list_builder(
5371        cte,
5372        instance=instance,
5373        arg="with",
5374        append=append,
5375        copy=copy,
5376        into=With,
5377        properties={"recursive": recursive or False},
5378    )
5379
5380
5381def _combine(
5382    expressions: t.Sequence[t.Optional[ExpOrStr]],
5383    operator: t.Type[Connector],
5384    dialect: DialectType = None,
5385    copy: bool = True,
5386    **opts,
5387) -> Expression:
5388    conditions = [
5389        condition(expression, dialect=dialect, copy=copy, **opts)
5390        for expression in expressions
5391        if expression is not None
5392    ]
5393
5394    this, *rest = conditions
5395    if rest:
5396        this = _wrap(this, Connector)
5397    for expression in rest:
5398        this = operator(this=this, expression=_wrap(expression, Connector))
5399
5400    return this
5401
5402
5403def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5404    return Paren(this=expression) if isinstance(expression, kind) else expression
5405
5406
5407def union(
5408    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5409) -> Union:
5410    """
5411    Initializes a syntax tree from one UNION expression.
5412
5413    Example:
5414        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5415        'SELECT * FROM foo UNION SELECT * FROM bla'
5416
5417    Args:
5418        left: the SQL code string corresponding to the left-hand side.
5419            If an `Expression` instance is passed, it will be used as-is.
5420        right: the SQL code string corresponding to the right-hand side.
5421            If an `Expression` instance is passed, it will be used as-is.
5422        distinct: set the DISTINCT flag if and only if this is true.
5423        dialect: the dialect used to parse the input expression.
5424        opts: other options to use to parse the input expressions.
5425
5426    Returns:
5427        The new Union instance.
5428    """
5429    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5430    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5431
5432    return Union(this=left, expression=right, distinct=distinct)
5433
5434
5435def intersect(
5436    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5437) -> Intersect:
5438    """
5439    Initializes a syntax tree from one INTERSECT expression.
5440
5441    Example:
5442        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5443        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5444
5445    Args:
5446        left: the SQL code string corresponding to the left-hand side.
5447            If an `Expression` instance is passed, it will be used as-is.
5448        right: the SQL code string corresponding to the right-hand side.
5449            If an `Expression` instance is passed, it will be used as-is.
5450        distinct: set the DISTINCT flag if and only if this is true.
5451        dialect: the dialect used to parse the input expression.
5452        opts: other options to use to parse the input expressions.
5453
5454    Returns:
5455        The new Intersect instance.
5456    """
5457    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5458    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5459
5460    return Intersect(this=left, expression=right, distinct=distinct)
5461
5462
5463def except_(
5464    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5465) -> Except:
5466    """
5467    Initializes a syntax tree from one EXCEPT expression.
5468
5469    Example:
5470        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5471        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5472
5473    Args:
5474        left: the SQL code string corresponding to the left-hand side.
5475            If an `Expression` instance is passed, it will be used as-is.
5476        right: the SQL code string corresponding to the right-hand side.
5477            If an `Expression` instance is passed, it will be used as-is.
5478        distinct: set the DISTINCT flag if and only if this is true.
5479        dialect: the dialect used to parse the input expression.
5480        opts: other options to use to parse the input expressions.
5481
5482    Returns:
5483        The new Except instance.
5484    """
5485    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5486    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5487
5488    return Except(this=left, expression=right, distinct=distinct)
5489
5490
5491def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5492    """
5493    Initializes a syntax tree from one or multiple SELECT expressions.
5494
5495    Example:
5496        >>> select("col1", "col2").from_("tbl").sql()
5497        'SELECT col1, col2 FROM tbl'
5498
5499    Args:
5500        *expressions: the SQL code string to parse as the expressions of a
5501            SELECT statement. If an Expression instance is passed, this is used as-is.
5502        dialect: the dialect used to parse the input expressions (in the case that an
5503            input expression is a SQL string).
5504        **opts: other options to use to parse the input expressions (again, in the case
5505            that an input expression is a SQL string).
5506
5507    Returns:
5508        Select: the syntax tree for the SELECT statement.
5509    """
5510    return Select().select(*expressions, dialect=dialect, **opts)
5511
5512
5513def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5514    """
5515    Initializes a syntax tree from a FROM expression.
5516
5517    Example:
5518        >>> from_("tbl").select("col1", "col2").sql()
5519        'SELECT col1, col2 FROM tbl'
5520
5521    Args:
5522        *expression: the SQL code string to parse as the FROM expressions of a
5523            SELECT statement. If an Expression instance is passed, this is used as-is.
5524        dialect: the dialect used to parse the input expression (in the case that the
5525            input expression is a SQL string).
5526        **opts: other options to use to parse the input expressions (again, in the case
5527            that the input expression is a SQL string).
5528
5529    Returns:
5530        Select: the syntax tree for the SELECT statement.
5531    """
5532    return Select().from_(expression, dialect=dialect, **opts)
5533
5534
5535def update(
5536    table: str | Table,
5537    properties: dict,
5538    where: t.Optional[ExpOrStr] = None,
5539    from_: t.Optional[ExpOrStr] = None,
5540    dialect: DialectType = None,
5541    **opts,
5542) -> Update:
5543    """
5544    Creates an update statement.
5545
5546    Example:
5547        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5548        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5549
5550    Args:
5551        *properties: dictionary of properties to set which are
5552            auto converted to sql objects eg None -> NULL
5553        where: sql conditional parsed into a WHERE statement
5554        from_: sql statement parsed into a FROM statement
5555        dialect: the dialect used to parse the input expressions.
5556        **opts: other options to use to parse the input expressions.
5557
5558    Returns:
5559        Update: the syntax tree for the UPDATE statement.
5560    """
5561    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5562    update_expr.set(
5563        "expressions",
5564        [
5565            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5566            for k, v in properties.items()
5567        ],
5568    )
5569    if from_:
5570        update_expr.set(
5571            "from",
5572            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5573        )
5574    if isinstance(where, Condition):
5575        where = Where(this=where)
5576    if where:
5577        update_expr.set(
5578            "where",
5579            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5580        )
5581    return update_expr
5582
5583
5584def delete(
5585    table: ExpOrStr,
5586    where: t.Optional[ExpOrStr] = None,
5587    returning: t.Optional[ExpOrStr] = None,
5588    dialect: DialectType = None,
5589    **opts,
5590) -> Delete:
5591    """
5592    Builds a delete statement.
5593
5594    Example:
5595        >>> delete("my_table", where="id > 1").sql()
5596        'DELETE FROM my_table WHERE id > 1'
5597
5598    Args:
5599        where: sql conditional parsed into a WHERE statement
5600        returning: sql conditional parsed into a RETURNING statement
5601        dialect: the dialect used to parse the input expressions.
5602        **opts: other options to use to parse the input expressions.
5603
5604    Returns:
5605        Delete: the syntax tree for the DELETE statement.
5606    """
5607    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5608    if where:
5609        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5610    if returning:
5611        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5612    return delete_expr
5613
5614
5615def insert(
5616    expression: ExpOrStr,
5617    into: ExpOrStr,
5618    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5619    overwrite: t.Optional[bool] = None,
5620    dialect: DialectType = None,
5621    copy: bool = True,
5622    **opts,
5623) -> Insert:
5624    """
5625    Builds an INSERT statement.
5626
5627    Example:
5628        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5629        'INSERT INTO tbl VALUES (1, 2, 3)'
5630
5631    Args:
5632        expression: the sql string or expression of the INSERT statement
5633        into: the tbl to insert data to.
5634        columns: optionally the table's column names.
5635        overwrite: whether to INSERT OVERWRITE or not.
5636        dialect: the dialect used to parse the input expressions.
5637        copy: whether or not to copy the expression.
5638        **opts: other options to use to parse the input expressions.
5639
5640    Returns:
5641        Insert: the syntax tree for the INSERT statement.
5642    """
5643    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5644    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5645
5646    if columns:
5647        this = _apply_list_builder(
5648            *columns,
5649            instance=Schema(this=this),
5650            arg="expressions",
5651            into=Identifier,
5652            copy=False,
5653            dialect=dialect,
5654            **opts,
5655        )
5656
5657    return Insert(this=this, expression=expr, overwrite=overwrite)
5658
5659
5660def condition(
5661    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5662) -> Condition:
5663    """
5664    Initialize a logical condition expression.
5665
5666    Example:
5667        >>> condition("x=1").sql()
5668        'x = 1'
5669
5670        This is helpful for composing larger logical syntax trees:
5671        >>> where = condition("x=1")
5672        >>> where = where.and_("y=1")
5673        >>> Select().from_("tbl").select("*").where(where).sql()
5674        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5675
5676    Args:
5677        *expression: the SQL code string to parse.
5678            If an Expression instance is passed, this is used as-is.
5679        dialect: the dialect used to parse the input expression (in the case that the
5680            input expression is a SQL string).
5681        copy: Whether or not to copy `expression` (only applies to expressions).
5682        **opts: other options to use to parse the input expressions (again, in the case
5683            that the input expression is a SQL string).
5684
5685    Returns:
5686        The new Condition instance
5687    """
5688    return maybe_parse(
5689        expression,
5690        into=Condition,
5691        dialect=dialect,
5692        copy=copy,
5693        **opts,
5694    )
5695
5696
5697def and_(
5698    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5699) -> Condition:
5700    """
5701    Combine multiple conditions with an AND logical operator.
5702
5703    Example:
5704        >>> and_("x=1", and_("y=1", "z=1")).sql()
5705        'x = 1 AND (y = 1 AND z = 1)'
5706
5707    Args:
5708        *expressions: the SQL code strings to parse.
5709            If an Expression instance is passed, this is used as-is.
5710        dialect: the dialect used to parse the input expression.
5711        copy: whether or not to copy `expressions` (only applies to Expressions).
5712        **opts: other options to use to parse the input expressions.
5713
5714    Returns:
5715        And: the new condition
5716    """
5717    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5718
5719
5720def or_(
5721    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5722) -> Condition:
5723    """
5724    Combine multiple conditions with an OR logical operator.
5725
5726    Example:
5727        >>> or_("x=1", or_("y=1", "z=1")).sql()
5728        'x = 1 OR (y = 1 OR z = 1)'
5729
5730    Args:
5731        *expressions: the SQL code strings to parse.
5732            If an Expression instance is passed, this is used as-is.
5733        dialect: the dialect used to parse the input expression.
5734        copy: whether or not to copy `expressions` (only applies to Expressions).
5735        **opts: other options to use to parse the input expressions.
5736
5737    Returns:
5738        Or: the new condition
5739    """
5740    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5741
5742
5743def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5744    """
5745    Wrap a condition with a NOT operator.
5746
5747    Example:
5748        >>> not_("this_suit='black'").sql()
5749        "NOT this_suit = 'black'"
5750
5751    Args:
5752        expression: the SQL code string to parse.
5753            If an Expression instance is passed, this is used as-is.
5754        dialect: the dialect used to parse the input expression.
5755        copy: whether to copy the expression or not.
5756        **opts: other options to use to parse the input expressions.
5757
5758    Returns:
5759        The new condition.
5760    """
5761    this = condition(
5762        expression,
5763        dialect=dialect,
5764        copy=copy,
5765        **opts,
5766    )
5767    return Not(this=_wrap(this, Connector))
5768
5769
5770def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5771    """
5772    Wrap an expression in parentheses.
5773
5774    Example:
5775        >>> paren("5 + 3").sql()
5776        '(5 + 3)'
5777
5778    Args:
5779        expression: the SQL code string to parse.
5780            If an Expression instance is passed, this is used as-is.
5781        copy: whether to copy the expression or not.
5782
5783    Returns:
5784        The wrapped expression.
5785    """
5786    return Paren(this=maybe_parse(expression, copy=copy))
5787
5788
5789SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5790
5791
5792@t.overload
5793def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5794    ...
5795
5796
5797@t.overload
5798def to_identifier(
5799    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5800) -> Identifier:
5801    ...
5802
5803
5804def to_identifier(name, quoted=None, copy=True):
5805    """Builds an identifier.
5806
5807    Args:
5808        name: The name to turn into an identifier.
5809        quoted: Whether or not force quote the identifier.
5810        copy: Whether or not to copy a passed in Identefier node.
5811
5812    Returns:
5813        The identifier ast node.
5814    """
5815
5816    if name is None:
5817        return None
5818
5819    if isinstance(name, Identifier):
5820        identifier = maybe_copy(name, copy)
5821    elif isinstance(name, str):
5822        identifier = Identifier(
5823            this=name,
5824            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5825        )
5826    else:
5827        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5828    return identifier
5829
5830
5831INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5832
5833
5834def to_interval(interval: str | Literal) -> Interval:
5835    """Builds an interval expression from a string like '1 day' or '5 months'."""
5836    if isinstance(interval, Literal):
5837        if not interval.is_string:
5838            raise ValueError("Invalid interval string.")
5839
5840        interval = interval.this
5841
5842    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5843
5844    if not interval_parts:
5845        raise ValueError("Invalid interval string.")
5846
5847    return Interval(
5848        this=Literal.string(interval_parts.group(1)),
5849        unit=Var(this=interval_parts.group(2)),
5850    )
5851
5852
5853@t.overload
5854def to_table(sql_path: str | Table, **kwargs) -> Table:
5855    ...
5856
5857
5858@t.overload
5859def to_table(sql_path: None, **kwargs) -> None:
5860    ...
5861
5862
5863def to_table(
5864    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5865) -> t.Optional[Table]:
5866    """
5867    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5868    If a table is passed in then that table is returned.
5869
5870    Args:
5871        sql_path: a `[catalog].[schema].[table]` string.
5872        dialect: the source dialect according to which the table name will be parsed.
5873        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5874
5875    Returns:
5876        A table expression.
5877    """
5878    if sql_path is None or isinstance(sql_path, Table):
5879        return sql_path
5880    if not isinstance(sql_path, str):
5881        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5882
5883    table = maybe_parse(sql_path, into=Table, dialect=dialect)
5884    if table:
5885        for k, v in kwargs.items():
5886            table.set(k, v)
5887
5888    return table
5889
5890
5891def to_column(sql_path: str | Column, **kwargs) -> Column:
5892    """
5893    Create a column from a `[table].[column]` sql path. Schema is optional.
5894
5895    If a column is passed in then that column is returned.
5896
5897    Args:
5898        sql_path: `[table].[column]` string
5899    Returns:
5900        Table: A column expression
5901    """
5902    if sql_path is None or isinstance(sql_path, Column):
5903        return sql_path
5904    if not isinstance(sql_path, str):
5905        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
5906    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
5907
5908
5909def alias_(
5910    expression: ExpOrStr,
5911    alias: str | Identifier,
5912    table: bool | t.Sequence[str | Identifier] = False,
5913    quoted: t.Optional[bool] = None,
5914    dialect: DialectType = None,
5915    copy: bool = True,
5916    **opts,
5917):
5918    """Create an Alias expression.
5919
5920    Example:
5921        >>> alias_('foo', 'bar').sql()
5922        'foo AS bar'
5923
5924        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
5925        '(SELECT 1, 2) AS bar(a, b)'
5926
5927    Args:
5928        expression: the SQL code strings to parse.
5929            If an Expression instance is passed, this is used as-is.
5930        alias: the alias name to use. If the name has
5931            special characters it is quoted.
5932        table: Whether or not to create a table alias, can also be a list of columns.
5933        quoted: whether or not to quote the alias
5934        dialect: the dialect used to parse the input expression.
5935        copy: Whether or not to copy the expression.
5936        **opts: other options to use to parse the input expressions.
5937
5938    Returns:
5939        Alias: the aliased expression
5940    """
5941    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5942    alias = to_identifier(alias, quoted=quoted)
5943
5944    if table:
5945        table_alias = TableAlias(this=alias)
5946        exp.set("alias", table_alias)
5947
5948        if not isinstance(table, bool):
5949            for column in table:
5950                table_alias.append("columns", to_identifier(column, quoted=quoted))
5951
5952        return exp
5953
5954    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
5955    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
5956    # for the complete Window expression.
5957    #
5958    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
5959
5960    if "alias" in exp.arg_types and not isinstance(exp, Window):
5961        exp.set("alias", alias)
5962        return exp
5963    return Alias(this=exp, alias=alias)
5964
5965
5966def subquery(
5967    expression: ExpOrStr,
5968    alias: t.Optional[Identifier | str] = None,
5969    dialect: DialectType = None,
5970    **opts,
5971) -> Select:
5972    """
5973    Build a subquery expression.
5974
5975    Example:
5976        >>> subquery('select x from tbl', 'bar').select('x').sql()
5977        'SELECT x FROM (SELECT x FROM tbl) AS bar'
5978
5979    Args:
5980        expression: the SQL code strings to parse.
5981            If an Expression instance is passed, this is used as-is.
5982        alias: the alias name to use.
5983        dialect: the dialect used to parse the input expression.
5984        **opts: other options to use to parse the input expressions.
5985
5986    Returns:
5987        A new Select instance with the subquery expression included.
5988    """
5989
5990    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
5991    return Select().from_(expression, dialect=dialect, **opts)
5992
5993
5994def column(
5995    col: str | Identifier,
5996    table: t.Optional[str | Identifier] = None,
5997    db: t.Optional[str | Identifier] = None,
5998    catalog: t.Optional[str | Identifier] = None,
5999    quoted: t.Optional[bool] = None,
6000) -> Column:
6001    """
6002    Build a Column.
6003
6004    Args:
6005        col: Column name.
6006        table: Table name.
6007        db: Database name.
6008        catalog: Catalog name.
6009        quoted: Whether to force quotes on the column's identifiers.
6010
6011    Returns:
6012        The new Column instance.
6013    """
6014    return Column(
6015        this=to_identifier(col, quoted=quoted),
6016        table=to_identifier(table, quoted=quoted),
6017        db=to_identifier(db, quoted=quoted),
6018        catalog=to_identifier(catalog, quoted=quoted),
6019    )
6020
6021
6022def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
6023    """Cast an expression to a data type.
6024
6025    Example:
6026        >>> cast('x + 1', 'int').sql()
6027        'CAST(x + 1 AS INT)'
6028
6029    Args:
6030        expression: The expression to cast.
6031        to: The datatype to cast to.
6032
6033    Returns:
6034        The new Cast instance.
6035    """
6036    expression = maybe_parse(expression, **opts)
6037    data_type = DataType.build(to, **opts)
6038    expression = Cast(this=expression, to=data_type)
6039    expression.type = data_type
6040    return expression
6041
6042
6043def table_(
6044    table: Identifier | str,
6045    db: t.Optional[Identifier | str] = None,
6046    catalog: t.Optional[Identifier | str] = None,
6047    quoted: t.Optional[bool] = None,
6048    alias: t.Optional[Identifier | str] = None,
6049) -> Table:
6050    """Build a Table.
6051
6052    Args:
6053        table: Table name.
6054        db: Database name.
6055        catalog: Catalog name.
6056        quote: Whether to force quotes on the table's identifiers.
6057        alias: Table's alias.
6058
6059    Returns:
6060        The new Table instance.
6061    """
6062    return Table(
6063        this=to_identifier(table, quoted=quoted) if table else None,
6064        db=to_identifier(db, quoted=quoted) if db else None,
6065        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6066        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6067    )
6068
6069
6070def values(
6071    values: t.Iterable[t.Tuple[t.Any, ...]],
6072    alias: t.Optional[str] = None,
6073    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6074) -> Values:
6075    """Build VALUES statement.
6076
6077    Example:
6078        >>> values([(1, '2')]).sql()
6079        "VALUES (1, '2')"
6080
6081    Args:
6082        values: values statements that will be converted to SQL
6083        alias: optional alias
6084        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6085         If either are provided then an alias is also required.
6086
6087    Returns:
6088        Values: the Values expression object
6089    """
6090    if columns and not alias:
6091        raise ValueError("Alias is required when providing columns")
6092
6093    return Values(
6094        expressions=[convert(tup) for tup in values],
6095        alias=(
6096            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6097            if columns
6098            else (TableAlias(this=to_identifier(alias)) if alias else None)
6099        ),
6100    )
6101
6102
6103def var(name: t.Optional[ExpOrStr]) -> Var:
6104    """Build a SQL variable.
6105
6106    Example:
6107        >>> repr(var('x'))
6108        '(VAR this: x)'
6109
6110        >>> repr(var(column('x', table='y')))
6111        '(VAR this: x)'
6112
6113    Args:
6114        name: The name of the var or an expression who's name will become the var.
6115
6116    Returns:
6117        The new variable node.
6118    """
6119    if not name:
6120        raise ValueError("Cannot convert empty name into var.")
6121
6122    if isinstance(name, Expression):
6123        name = name.name
6124    return Var(this=name)
6125
6126
6127def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6128    """Build ALTER TABLE... RENAME... expression
6129
6130    Args:
6131        old_name: The old name of the table
6132        new_name: The new name of the table
6133
6134    Returns:
6135        Alter table expression
6136    """
6137    old_table = to_table(old_name)
6138    new_table = to_table(new_name)
6139    return AlterTable(
6140        this=old_table,
6141        actions=[
6142            RenameTable(this=new_table),
6143        ],
6144    )
6145
6146
6147def convert(value: t.Any, copy: bool = False) -> Expression:
6148    """Convert a python value into an expression object.
6149
6150    Raises an error if a conversion is not possible.
6151
6152    Args:
6153        value: A python object.
6154        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6155
6156    Returns:
6157        Expression: the equivalent expression object.
6158    """
6159    if isinstance(value, Expression):
6160        return maybe_copy(value, copy)
6161    if isinstance(value, str):
6162        return Literal.string(value)
6163    if isinstance(value, bool):
6164        return Boolean(this=value)
6165    if value is None or (isinstance(value, float) and math.isnan(value)):
6166        return NULL
6167    if isinstance(value, numbers.Number):
6168        return Literal.number(value)
6169    if isinstance(value, datetime.datetime):
6170        datetime_literal = Literal.string(
6171            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6172        )
6173        return TimeStrToTime(this=datetime_literal)
6174    if isinstance(value, datetime.date):
6175        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6176        return DateStrToDate(this=date_literal)
6177    if isinstance(value, tuple):
6178        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6179    if isinstance(value, list):
6180        return Array(expressions=[convert(v, copy=copy) for v in value])
6181    if isinstance(value, dict):
6182        return Map(
6183            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6184            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6185        )
6186    raise ValueError(f"Cannot convert {value}")
6187
6188
6189def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6190    """
6191    Replace children of an expression with the result of a lambda fun(child) -> exp.
6192    """
6193    for k, v in expression.args.items():
6194        is_list_arg = type(v) is list
6195
6196        child_nodes = v if is_list_arg else [v]
6197        new_child_nodes = []
6198
6199        for cn in child_nodes:
6200            if isinstance(cn, Expression):
6201                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6202                    new_child_nodes.append(child_node)
6203                    child_node.parent = expression
6204                    child_node.arg_key = k
6205            else:
6206                new_child_nodes.append(cn)
6207
6208        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6209
6210
6211def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6212    """
6213    Return all table names referenced through columns in an expression.
6214
6215    Example:
6216        >>> import sqlglot
6217        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6218        ['a', 'c']
6219
6220    Args:
6221        expression: expression to find table names.
6222        exclude: a table name to exclude
6223
6224    Returns:
6225        A list of unique names.
6226    """
6227    return {
6228        table
6229        for table in (column.table for column in expression.find_all(Column))
6230        if table and table != exclude
6231    }
6232
6233
6234def table_name(table: Table | str, dialect: DialectType = None) -> str:
6235    """Get the full name of a table as a string.
6236
6237    Args:
6238        table: Table expression node or string.
6239        dialect: The dialect to generate the table name for.
6240
6241    Examples:
6242        >>> from sqlglot import exp, parse_one
6243        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6244        'a.b.c'
6245
6246    Returns:
6247        The table name.
6248    """
6249
6250    table = maybe_parse(table, into=Table, dialect=dialect)
6251
6252    if not table:
6253        raise ValueError(f"Cannot parse {table}")
6254
6255    return ".".join(
6256        part.sql(dialect=dialect, identify=True)
6257        if not SAFE_IDENTIFIER_RE.match(part.name)
6258        else part.name
6259        for part in table.parts
6260    )
6261
6262
6263def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6264    """Replace all tables in expression according to the mapping.
6265
6266    Args:
6267        expression: expression node to be transformed and replaced.
6268        mapping: mapping of table names.
6269        copy: whether or not to copy the expression.
6270
6271    Examples:
6272        >>> from sqlglot import exp, parse_one
6273        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6274        'SELECT * FROM c'
6275
6276    Returns:
6277        The mapped expression.
6278    """
6279
6280    def _replace_tables(node: Expression) -> Expression:
6281        if isinstance(node, Table):
6282            new_name = mapping.get(table_name(node))
6283            if new_name:
6284                return to_table(
6285                    new_name,
6286                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6287                )
6288        return node
6289
6290    return expression.transform(_replace_tables, copy=copy)
6291
6292
6293def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6294    """Replace placeholders in an expression.
6295
6296    Args:
6297        expression: expression node to be transformed and replaced.
6298        args: positional names that will substitute unnamed placeholders in the given order.
6299        kwargs: keyword arguments that will substitute named placeholders.
6300
6301    Examples:
6302        >>> from sqlglot import exp, parse_one
6303        >>> replace_placeholders(
6304        ...     parse_one("select * from :tbl where ? = ?"),
6305        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6306        ... ).sql()
6307        "SELECT * FROM foo WHERE str_col = 'b'"
6308
6309    Returns:
6310        The mapped expression.
6311    """
6312
6313    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6314        if isinstance(node, Placeholder):
6315            if node.name:
6316                new_name = kwargs.get(node.name)
6317                if new_name:
6318                    return convert(new_name)
6319            else:
6320                try:
6321                    return convert(next(args))
6322                except StopIteration:
6323                    pass
6324        return node
6325
6326    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6327
6328
6329def expand(
6330    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6331) -> Expression:
6332    """Transforms an expression by expanding all referenced sources into subqueries.
6333
6334    Examples:
6335        >>> from sqlglot import parse_one
6336        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6337        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6338
6339        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6340        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6341
6342    Args:
6343        expression: The expression to expand.
6344        sources: A dictionary of name to Subqueryables.
6345        copy: Whether or not to copy the expression during transformation. Defaults to True.
6346
6347    Returns:
6348        The transformed expression.
6349    """
6350
6351    def _expand(node: Expression):
6352        if isinstance(node, Table):
6353            name = table_name(node)
6354            source = sources.get(name)
6355            if source:
6356                subquery = source.subquery(node.alias or name)
6357                subquery.comments = [f"source: {name}"]
6358                return subquery.transform(_expand, copy=False)
6359        return node
6360
6361    return expression.transform(_expand, copy=copy)
6362
6363
6364def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6365    """
6366    Returns a Func expression.
6367
6368    Examples:
6369        >>> func("abs", 5).sql()
6370        'ABS(5)'
6371
6372        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6373        'CAST(5 AS DOUBLE)'
6374
6375    Args:
6376        name: the name of the function to build.
6377        args: the args used to instantiate the function of interest.
6378        dialect: the source dialect.
6379        kwargs: the kwargs used to instantiate the function of interest.
6380
6381    Note:
6382        The arguments `args` and `kwargs` are mutually exclusive.
6383
6384    Returns:
6385        An instance of the function of interest, or an anonymous function, if `name` doesn't
6386        correspond to an existing `sqlglot.expressions.Func` class.
6387    """
6388    if args and kwargs:
6389        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6390
6391    from sqlglot.dialects.dialect import Dialect
6392
6393    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6394    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6395
6396    parser = Dialect.get_or_raise(dialect)().parser()
6397    from_args_list = parser.FUNCTIONS.get(name.upper())
6398
6399    if from_args_list:
6400        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6401    else:
6402        kwargs = kwargs or {"expressions": converted}
6403        function = Anonymous(this=name, **kwargs)
6404
6405    for error_message in function.error_messages(converted):
6406        raise ValueError(error_message)
6407
6408    return function
6409
6410
6411def true() -> Boolean:
6412    """
6413    Returns a true Boolean expression.
6414    """
6415    return Boolean(this=True)
6416
6417
6418def false() -> Boolean:
6419    """
6420    Returns a false Boolean expression.
6421    """
6422    return Boolean(this=False)
6423
6424
6425def null() -> Null:
6426    """
6427    Returns a Null expression.
6428    """
6429    return Null()
6430
6431
6432# TODO: deprecate this
6433TRUE = Boolean(this=True)
6434FALSE = Boolean(this=False)
6435NULL = Null()